Email from Excel 2007 / 2010

Posted on 2012-08-15
Medium Priority
Last Modified: 2012-08-16
I need some VBA code to format the body of an email where the text is created within Excel and then sends the e-mail via Outlook. All of my  VBA to achieve thsi is in Excel

The basic process works well but the e-mail needs to have specific formatting for the text.

Any help will be very much appreciated.
Question by:NormanMitchell
  • 2
  • 2
LVL 24

Expert Comment

ID: 38295452
Could you post your existing code as there are many ways to Use Excel & Outlook together.

Author Comment

ID: 38295560
Here is an extract from the code:

'Set parameters
    Set objExcel = Application
    Set wksWorksheet = objExcel.ActiveSheet
    Set wkbWorkbook = objExcel.ActiveWorkbook

    strEMailAddress = ActiveSheet.Cells(18, 4)

 'Build the body of the e-mail
    strPara1 = "Text for paragraph 1"
    strPara2 = "Text for paragraph 2"
    strPara3 = "Text for paragraph 3"
    strPara4 = "Text for paragraph 4"
    strSalutation = "Best regards" & vbcr & vbcr & "Norman Mitchell"
    'E-mail text
    If strPara1 <> "" Then
        strBody = strPara1
    End If
    If strPara2 <> "" Then
        strBody = strBody & vbCr & vbCr & strPara2
    End If
    If strPara3 <> "" Then
        strBody = strBody & vbCr & vbCr & strPara3
    End If
    If strPara4 <> "" Then
        strBody = strBody & vbCr & vbCr & strPara4
    End If
    strBody = strBody & vbCr & vbCr & strSalutation _
                & vbCr & vbCr & Application.UserName

'Send e-mail
    Set olApp = CreateObject("Outlook.Application")
    Set olNameSpace = olApp.GetNamespace("MAPI")
    Set olFolder = olNameSpace.GetDefaultFolder(6)
    Set olMail = olApp.CreateItem(0)
    With olMail
        .Subject = strSubject
        .Recipients.Add strEMailAddress
        .Body = strBody

        If booSave = True Then
        End If
    End With
'Remove objects
    Set objExcel = Nothing
    Set wksWorksheet = Nothing
    Set wkbWorkbook = Nothing

    Set olApp = Nothing
    Set olNameSpace = Nothing
    Set olFolder = Nothing
    Set olMail = Nothing
LVL 24

Accepted Solution

Steve earned 1500 total points
ID: 38295645
in place of using .Body = strBody have you tried using :

.HTMLBody = strBody
.BodyFormat = 3 '1=Plain text 2=HTML 3=RichText 

Open in new window


Author Closing Comment

ID: 38300783
Not the complete answer but it pointed me in the right direction. Many thanks

Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

This article lists the top 5 trialware OST to PST Converter Tools. These tools save a lot of time for users when they want to convert OST to PST after their Exchange server is no longer available or other critical issues with Exchange server or impo…
Are you looking for the options available for exporting EDB files to PST? You may be confused as they are different in different Exchange versions. Here, I will discuss some options available.
Many of my clients call in with monstrous Gmail overloading issues with Outlook. A quick tip is to turn off the All Mail and Important folders from synching. Here is a quick video I made to show you how to turn off these and other folders in Gmail s…
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

809 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