Consulting

Results 1 to 5 of 5

Thread: Gmail Macro

  1. #1
    VBAX Tutor
    Joined
    Sep 2008
    Posts
    213
    Location

    Gmail Macro

    I have attempted to use the following macro I found 'ready to use' to send from Excel through my test gmail account, however when I run the macro I recieve a " system error &H80040211 (-2147220975)"
    Below is the code exactly as I tried to use it apart from the email address and password

    [VBA]

    Sub CDO_Mail_Small_Text_2()
    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/con...ion/smtpusessl") = True
    .Item("http://schemas.microsoft.com/cdo/con...tpauthenticate") = 1
    .Item("http://schemas.microsoft.com/cdo/con...n/sendusername") = "XXXX@gmail.com"
    .Item("http://schemas.microsoft.com/cdo/con...n/sendpassword") = "XXXXXXXXXX"
    .Item("http://schemas.microsoft.com/cdo/con...ion/smtpserver") = "smtp.gmail.com"
    .Item("http://schemas.microsoft.com/cdo/con...tion/sendusing") = 2
    .Item("http://schemas.microsoft.com/cdo/con...smtpserverport") = 465
    .Update
    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 = "deane@saasset.co.jp"
    .CC = ""
    .BCC = ""
    ' Note: The reply address is not working if you use this Gmail example
    ' It will use your Gmail address automatic. But you can add this line
    ' to change the reply address .ReplyTo = "Reply@something.nl"
    .From = """YourName"" <Reply@something.nl>"
    .Subject = "Important message"
    .TextBody = strbody
    .Send
    End With
    End Sub

    [/VBA]

  2. #2
    VBAX Mentor
    Joined
    Feb 2009
    Posts
    493
    Location
    worked fine for me. I have you enabled external access in Gmail?
    -----------------------------------------
    The more you learn about something the more you know you have much to learn.

  3. #3
    VBAX Tutor
    Joined
    Sep 2008
    Posts
    213
    Location
    Brian, I tried this on a different PC and it worked first go. Not sure what the issue was, but seems to be working ok.

  4. #4
    VBAX Tutor
    Joined
    Sep 2008
    Posts
    213
    Location
    I'd like to change the content of the email that is sent from the fixed content in this example to some of the values in the spread sheet. How do I change the following code to achieve this?

    [VBA]
    "This is line 1" & vbNewLine & _
    "This is line 2" & vbNewLine & _
    "This is line 3" & vbNewLine & _
    "This is line 4"
    [/VBA]

    Thanks!!

  5. #5
    VBAX Mentor
    Joined
    Feb 2009
    Posts
    493
    Location
    [VBA]
    activesheet.range("A1").value & vbnewline & _
    activesheet.range("A2").value & vbnewline & _
    activesheet.range("B3").value & vbnewline & _
    activesheet.range("B4").value & vbnewline & _
    activesheet.range("C1").value
    [/VBA]
    -----------------------------------------
    The more you learn about something the more you know you have much to learn.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •