使用Gmail从Excel发送电子邮件 [英] email from Excel using Gmail

问题描述

我正在尝试在保存工作簿后自动生成电子邮件。我不想通过电子邮件发送工作簿，而只是通过电子邮件通知某人，说该工作簿有一个新条目，因此他们实际上必须打开它并做出响应（如果我可以链接到该文件的位置，可以正常工作的电子表格）。另外，该工作簿是共享的，因此多个人可以一次对其进行编辑，因此，我认为它不会保留为共享的，并且如果从电子邮件中下载，则不会继续更新。大约有25个人可以访问此电子表格，任何人都可以输入/编辑条目。最终，我希望它仅在特定列中输入/编辑数据然后保存后才发送电子邮件。

I'm trying to have an email auto generate after a workbook is saved. I don't want to send the workbook in the email, just an email notification to a list of people to say that it has a new entry so they actually have to open it and respond (if I could put a link to the location of the spreadsheet that would work). Also the workbook is "shared" so multiple people can edit it at once, so I don't think it will remain as "shared" and continue to update if it is downloaded from an email. About 25 people have access to this spreadsheet and anyone can enter/edit an entry. Ultimately, I'd like it to send an email only if data is entered/edited in a specific column and then saved.

我的代理商使用Gmail，但我们的电子邮件地址中没有 @ gmail.com 。取而代之的是，我们通过gmail使用了我们的 .gov 电子邮件地址。我不确定这是否有意义，但我想提一下。我搜索了多个在线论坛，但似乎找不到任何东西。

My agency uses Gmail but our email addresses do not have @gmail.com in them. Instead we are using our .gov email addresses through gmail somehow. I'm not sure if this is even relevant but thought I'd mention it. I've searched several online forums, but can't seem to find anything.

有人知道这样做的代码吗？

Does anyone know of any code to do this?

我是VBA的新手，我知道了电子邮件部分可以正常工作，但是我希望它在保存工作簿时通过电子邮件发送。这是我当前正在使用的代码：

I'm a novice with VBA and I got the email part to work but I want it to email when the workbook is saved. This is the code I am currently using:

Sub CDO_Mail_Small_Text()
    Dim iMsg As Object
    Dim iConf As Object
    Dim strbody As String
    '    Dim Flds As Variant

    Set iMsg = CreateObject("CDO.Message")
    Set iConf = CreateObject("CDO.Configuration")

    iConf.Load -1    ' CDO Source Defaults
    Set Flds = iConf.Fields
    With Flds
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 60
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "[e-mail]"
        .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "xxxxxxxxx"
        .Update    'Let CDO know we have change the default configuration for this message
    End With




    strbody = "Hi there" & vbNewLine & vbNewLine & _
              "This is line 1" & vbNewLine & _
              "This is line 2" & vbNewLine & _
              "This is line 3" & vbNewLine & _
              "This is line 4"

    With iMsg
        Set .Configuration = iConf
        .To = "[e-mail]"
        .CC = ""
        .BCC = ""
        .From = """name"" <[e-mail]>"
        .Subject = "test"
        .TextBody = strbody
        .Send
    End With

End Sub

我收到此错误

推荐答案

好吧，我发现如果将变量放在子对象之外，然后在第一个子对象中调用第二个子对象，例如以及为.fields配置添加.update（感谢Tim！），它的工作原理是：

Well I figured out that if I put the variables outside the subs and then call the second sub in the first sub, as well as adding in the .update for the .fields configuration (thanks Tim!), it works:

Dim iMsg As Object
Dim iConf As Object
Dim strbody As String

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Call CDO_Mail_Small_Text
End Sub

Private Sub CDO_Mail_Small_Text()

Set iMsg = CreateObject("CDO.Message")
Set iConf = CreateObject("CDO.Configuration")

iConf.Load -1    ' CDO Source Defaults
Set Flds = iConf.Fields
With Flds
  .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
  .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
  .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
  .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
  .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
  .Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 60
  .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "[e-mail]"
  .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "xxxxxxxxx"
  .Update 'Let CDO know we have changed the default configuration for this message
End With

strbody = "Hi there" & vbNewLine & vbNewLine & _
      "This is line 1" & vbNewLine & _
      "This is line 2" & vbNewLine & _
      "This is line 3" & vbNewLine & _
      "This is line 4"

With iMsg
  Set .Configuration = iConf
    .To = "[e-mail]"
    .CC = ""
    .BCC = ""
    .From = """name"" <[e-mail]>"
    .Subject = "test"
    .TextBody = strbody
    .Send
End With

End Sub

这篇关于使用Gmail从Excel发送电子邮件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！