Outlook Express - Excel VBA

Hello Experts,

I have my code written to email a workbook to me, but I am confused on how to add additonal information to create a body message using a c.range.value.  How do you set code to add body content.

It would be similiar to "Data was keyed by " & week1.Range("A1").value

    On Error GoTo NOSEND
    ActiveWorkbook.SendMail _
    Recipients:="mknierim@classicburgers.com", _
    Subject:="Daily Data #" & week1.Range("I1").Value
    Exit Sub
End Sub

My endusers are using OE 6.00 & Excel 2003.

Thanks mike637.
Who is Participating?
I had assumed from your use of Outlook Express that you are probably using a hosted mail service and the use of CDO the most appropriate way to go.  The following was built to ease your use, using Ron's tips I already posted.

Tested in Excel 2003+

I have built a mock up of your workbook.  You'll need to identify your mail server (e.g., for hotmail/live it would be smtp.live.com, gmail would be smtp.gmail.com, etc.)  Just Google for example "Hotmail SMTP" or "Gmail SMTP" using your email host to determine the mail server)

So, just fill out:
ServerAdd - the smtp server
EmailAdd - your email address (from address)
toEmail - email addresses (recipients) to send to, separated by commas
strBody - the body to send, can be regular text or HTML
EmailSubject - the email subject

The default server port is generally 25, so test that out and if it fails, change the port to 465.

Here's the code:
Option Explicit
Sub sendMailWithCDO()

Dim iMsg As Object
Dim iConf As Object
Dim Flds As Variant
Dim strBody As String
Dim ServerAdd As String
Dim EmailAdd As String
Dim emailPassword As String
Dim strHTML As String
Dim toEmail As String
Dim tempWkb As String
Dim week1 As Worksheet
Dim EmailSubject As String
Dim wkb As Workbook

    Set wkb = ThisWorkbook
    Set week1 = wkb.Worksheets("Week1")
    'Save a temporary copy of this workbook, for the email attachment
    tempWkb = ThisWorkbook.Path & "\" & Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - Len(getFileExt(ThisWorkbook.Name))) & "_tmp" & getFileExt(ThisWorkbook.Name)
    ThisWorkbook.SaveCopyAs Filename:=tempWkb
    ServerAdd = "smtp.live.com" 'This is for Hotmail/Live - Put your email server here (e.g., mail.yourhost.com, or smtp.yourhost.com)
    EmailAdd = "yourEmail@emailHost.com"    'Put your email address here
    toEmail = "mknierim@classicburgers.com" 'put your to: email addresses here (recipients) separated by commas
    strBody = "Data was keyed by " & week1.Range("A1").Value
    EmailSubject = "Daily Data #" & week1.Range("I1").Value
    emailPassword = InputBox("Please enter your email password for " & EmailAdd)

    If emailPassword = "" Then
        Exit Sub
    End If

    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/smtpusessl") = True
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = ServerAdd
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25 'Typically 25 which works for hotmail/live, or try 465
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = EmailAdd
        .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = emailPassword
    End With

    strHTML = "<HTML>"
    strHTML = strHTML & "<HEAD>"
    strHTML = strHTML & "<BODY>"
    strHTML = strHTML & "<b> " & strBody & "</b></br>"
    strHTML = strHTML & "</BODY>"
    strHTML = strHTML & "</HTML>"

    With iMsg
        Set .Configuration = iConf
        .To = toEmail
        .AddAttachment tempWkb 'sends this workbook
        .From = EmailAdd
        .Subject = EmailSubject
        .HTMLBody = strHTML
        'Alternatively, use:
        '.TextBody = strBody 'for a text based body
    End With
    'Now delete the temporary workbook
    Kill pathname:=tempWkb
End Sub
Private Function getFileExt(fname As String) As String
Dim i As Integer

    i = InStr(StrReverse(fname), ".")
    getFileExt = StrReverse(Left(StrReverse(fname), i))

End Function
Private Function getFileName(fname As String) As String
    getFileName = Left(fname, Len(fname) - Len(getFileExt(fname)))
End Function

Open in new window

See attached demonstration workbook.


There is no Body property associated with the SendMail method.

However, you could use CDO or Outlook if that's available.

Here's a primer on CDO emailing using Excel from Ron deBruin, I believe the definitive resource on all things Excel/Email related!

Here's the page he has on CDO :

Build a sample and if you're having problems, just ask and I'll assist.


mike637Author Commented:
Thanks Dave - Another "Expert" has saved me.  Thanks again.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.