Help with CDO

[andrewb90]

Hello all,


I am trying to be able to send mail using CDO, and am very much over my head for my current skill level. Any help would be greatly appreciated. Here is what I have so far, but I keep getting syntax errors.

Sub emailtest()Dim CDO_Mail As Object
Dim CDO_Config As Object
Dim SMTP_Config As Variant
Dim strSubject As String
Dim strFrom As String
Dim strTo As String
Dim strCc As String
Dim strBcc As String
Dim strBody As String


strSubject = "Results from Excel Spreadsheet"
strFrom = "zaxbys52401@gmail.com"
strTo = "zaxbys52401@gmail.com"
strCc = ""
strBcc = ""
strBody = "The total results for this quarter are: " & Str(Emails.Cells(2, 1))






Set CDO_Mail = CreateObject("CDO.Message")
On Error GoTo Error_Handling


Set CDO_Config = CreateObject("CDO.Configuration")
CDO_Config.Load -1


Set SMTP_Config = CDO_Config.Fields




With SMTP_Config
    .Item("http://schemas.Microsoft.Com/cdo/configuration/smtpusessl") = True
    .Item("http://schemas.Microsoft.Com/cdo/configuration/smtpauthenticate") = 1
    .Item("http://schemas.Microsoft.Com/cdo/configuration/sendusername") = "***@gmail.com"
    .Item("http://schemas.Microsoft.Com/cdo/configuration/sendpassword") = "***"
    .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
    'Put your server name below
   .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.Gmail.Com"
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465
    .Update


With CDO_Mail
    Set .Configuration = CDO_Config
End With




CDO_Mail.Subject = strSubject
CDO_Mail.From = strFrom
CDO_Mail.To = strTo
CDO_Mail.TextBody = strBody
CDO_Mail.CC = strCc
CDO_Mail.BCC = strBcc
CDO_Mail.Send


Error_Handling:
If Err.Description <> - Then MsgBox Err.Description


End Sub

 

[andrewb90]

So, I'm not sure exactly what this part is:

  Gmail "ken@gmail.com", "Ken", _    p & " ken", _
    "See attached file: " & pdf, _
    sTo, _
    "1@2.3", _
    pdf
End Sub

I get the first line is the email and password like the other code, but the second "ken" is that a password as well?
I am getting the error that says the message could not be sent to the SMTP server. From what I understand would that mean that either my login info or port/smtp settings would be incorrect?

 

[Kenneth Hobson]

As you enter each parameter value in the function, a popup shows the input variable name so you can see what they mean. So, the 2nd Ken is part of the subject string.

Did you reset your gmail security Setting to low as you said that you did earlier? I have a commented link that lets one jump to that setting to toggle it off or on. Otherwise, maybe you sent the wrong password or username likely.

 

[andrewb90]

Yup! I was still not entirely awake and I realized that I had a little typo.
So a few questions, could I change the "To" address to just one address and use the list in the BCC line? If I made that change would it cause problems?

Also, I wouldn't think this would make a difference, but I figured I should ask; If the email address is a email to text address (the email address that the cell carriers use to receive emails as text) would that change anything or should it send as normal?

 

[Kenneth Hobson]

Yes, but you should add an input parameter to the function for the bcc. You can see how I did it with the To field.

An email address is an email address. Verizon also uses vtext.com as another domain fwiw.

 

[andrewb90]

Turns out I figured out the BCC pretty easily after I kept trying. I was about to reply then I had one more idea, and sure enough it worked!!!

Is it possible to change the message body to a cell range? I tried this:

Sheets("Settings").Range("C9").Value, _

And it works but if I do C9:D9 it doesn't

 

[Kenneth Hobson]

You can either make a copy with vbTab separating each column and vbLF for the rows, or use RangeToHTML are two ways. I will have to show you later tonight whichever method you like best.

 

[andrewb90]

Ok the range to Html sounds good. Thank you!

 

[Kenneth Hobson]

See commented .HTMLbody for where to use the RangeToHTML in the cdo function. What you can do is to make another cdo function where a range is passed to an input parameter as a Range rather than the current String type. The routine would then make a call to this function for the .HTMLbody's value.

Mail Range/Selection in the body of the mail
or

Function RangetoHTML(rng As Range)
' Changed by Ron de Bruin 28-Oct-2006
' Working in Office 2000-2013
    Dim fso As Object
    Dim ts As Object
    Dim TempFile As String
    Dim TempWB As Workbook

    TempFile = Environ$("temp") & "\" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"

    'Copy the range and create a new workbook to past the data in
    rng.Copy
    Set TempWB = Workbooks.Add(1)
    With TempWB.Sheets(1)
        .Cells(1).PasteSpecial Paste:=8
        .Cells(1).PasteSpecial xlPasteValues, , False, False
        .Cells(1).PasteSpecial xlPasteFormats, , False, False
        .Cells(1).Select
        Application.CutCopyMode = False
        On Error Resume Next
        .DrawingObjects.Visible = True
        .DrawingObjects.Delete
        On Error GoTo 0
    End With

    'Publish the sheet to a htm file
    With TempWB.PublishObjects.Add( _
            SourceType:=xlSourceRange, _
            fileName:=TempFile, _
            sheet:=TempWB.Sheets(1).Name, _
            source:=TempWB.Sheets(1).UsedRange.Address, _
            HtmlType:=xlHtmlStatic)
        .Publish (True)
    End With

    'Read all data from the htm file into RangetoHTML
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
    RangetoHTML = ts.ReadAll
    ts.Close
    RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _
            "align=left x:publishsource=")

    'Close TempWB
    TempWB.Close savechanges:=False

    'Delete the htm file we used in this function
    Kill TempFile

    Set ts = Nothing
    Set fso = Nothing
    Set TempWB = Nothing
End Function

 

[andrewb90]

Awesome! thank you for all of your help!!!
I just have to tidy up my workbook and I think I'll have everything working!

 

[andrewb90]

So, is it possible to customize the message body based on which email addresses are being used?
Could I send the emails in row 6 of the employees page the information on row 6 of the print page? Additionally every email would need the info from rows 2:3 of the print page as that contains the dates.
Unless there is an easier way to organize the data so that it could appear in the message body?

Thanks!