CDO Email, specific cells in the body

[Oximoxi]

Hey all!

Is it possible to send email through SMTP server using CDO (no Outlook!) and insert specific cells in to the body of the email? I have functioning macro for sending emails, but I strugle with the body content. I would like to insert specific region in to the body of the email. Cells from columns A-N and from the last filled row. Is it possible? I can't figure out how to do it.

Thanks, Oxi.

 

[Oximoxi]

Hi,

yeah, the trick with F8 key helped! For some reason, the last row in the sheet is not the last filled row, but row no. 1728. When I fill this row, the information are in the body of the email as I wanted to, so the program is working perfectly! The only problem left to solve is with the definition of the last row. Is there a way to define it perhaps by condition "last non-empty row"? Not the last row in the worksheet?

Thanks a lot, Oxi.

 

[daverunt]

Use (LastRow-1, c) in the line of code I added in the previous post.

 

[Oximoxi]

Thank you! I'm working on it right now and I tried to use this:

Dim LastLine As Long
LastLine = Range("A1048576").End(xlUp).Row
LastRow = LastLine
FirstCol = 1
LastCol = Rng.Columns.Count

And now it's working perfectly! I'm just making a few final touches and then I'll post the full functional code.

 

[Oximoxi]

So here it is! Thank you all for help, especially mr. daverunt! This code generates email with last row in the body and highest value in column A in the subject. You can adjust it as you like.

Sub CDO_Mail()
    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
    Set Flds = iConf.Fields
    With Flds
        .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") = "sending email adress"
        .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "password"
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp"

        .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465
        .Update
    End With
    

Set Rng = ActiveWorkbook.ActiveSheet.UsedRange

Dim LastLine As Long
LastLine = Range("A1048576").End(xlUp).Row
LastRow = LastLine
FirstCol = 1
LastCol = Rng.Columns.Count


For c = FirstCol To LastCol
For Each cell In ActiveWorkbook.ActiveSheet.Cells(LastRow, c)
strTable = strTable & "  " & cell.Value
Next
Next

strBody = strTable


     With iMsg
        Set .Configuration = iConf
        .to = "recipient email adress"
        .CC = ""
        .BCC = ""
        .From = """Name of sender"" <sender adress>"
        .Subject = "Subject:" & Application.WorksheetFunction.Max(Columns("A"))
        .TextBody = strBody
        .Send
    End With
    
    Set iMsg = Nothing
    Set iConf = Nothing
    Set Flds = Nothing
End Sub

 

[daverunt]

Glad you got it working.

I find it better best to avoid specifying the "A104856" as you can use the macro in Excel versions with fewer rows.
You don't need 2 variables for the last row. LastRow/LastLine
Just use one or the other.

LastLine = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row