Solved

Outlook Express - Excel VBA

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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Granting full access permission allows users to access mailboxes present in their database. By giving full access permission one can open and read the content of any mailbox but cannot send emails from that mailbox.
Use these top 10 tips to master the art of email signature design. Create an email signature design that will easily wow recipients, promote your brand and highlight your professionalism.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

707 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

18 Experts available now in Live!

Get 1:1 Help Now