Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Outlook Express - Excel VBA

Posted on 2012-03-17
3
Medium Priority
?
409 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 42

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 42

Accepted Solution

by:
dlmille earned 2000 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

The core idea of this article is to make you acquainted with the best way in which you can export Exchange mailbox to PST format.
There can be many situations demanding the conversion of Outlook OST files to PST format and as such, there is no shortage of automated tools to perform this conversion. However, what makes Stellar OST to PST converter stand above the rest? Let us e…
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Suggested Courses

886 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