Solved

Outlook Express - Excel VBA

Posted on 2012-03-17
3
352 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

This process describes the steps required to Import and Export data from and to .pst files using Exchange 2010. We can use these steps to export data from a user to a .pst file, import data back to the same or a different user, or even import data t…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

919 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now