Solved

Outlook Express - Excel VBA

Posted on 2012-03-17
3
365 Views
Last Modified: 2012-03-18
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

Private Sub EMAILKNIERIM()
       
    On Error GoTo NOSEND
    ActiveWorkbook.SendMail _
    Recipients:="mknierim@classicburgers.com", _
    Subject:="Daily Data #" & week1.Range("I1").Value
'
    Exit Sub
       
NOSEND:
  MsgBox ("NOT SENT SUCCESSFULLY - SEND MANUALLY AS AN ATTACHMENT")
End Sub

My endusers are using OE 6.00 & Excel 2003.

Thanks mike637.
0
Comment
Question by:mike637
  • 2
3 Comments
 
LVL 41

Expert Comment

by:dlmille
ID: 37733426
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!
http://www.rondebruin.nl/sendmail.htm


Here's the page he has on CDO :
http://www.rondebruin.nl/cdo.htm

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

Cheers,

Dave
0
 
LVL 41

Accepted Solution

by:
dlmille earned 500 total points
ID: 37734188
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
        .Update
    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
        .Send
    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.

Cheers,

Dave
sendWorkbookViaCDO-r1.xls
0
 

Author Closing Comment

by:mike637
ID: 37735566
Thanks Dave - Another "Expert" has saved me.  Thanks again.
mike637
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Are you unable to connect or configure Hotmail email account in Microsoft Outlook 2010, 2007? Or Outlook.com emails are not downloading to Outlook? Lets’ see the problem and resolve Outlook Connector error syncing folder hierarchy (0x8004102A).
MS Outlook is a world-class email client application that is mainly used for e-communication globally.  In this article, we will discuss the basic idea about MS Outlook, its advanced features, and types of MS Outlook File formats.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
CodeTwo Sync for iCloud (http://www.codetwo.com/sync-for-icloud?sts=6554) automatically synchronizes your Outlook 2016, 2013, 2010 or 2007 folders with iCloud folders available via iCloud Control Panel. This lets you automatically sync them with…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question