Email from Excel 2007 / 2010

Posted on 2012-08-15
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
    LVL 24

    Expert Comment

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

    Author Comment

    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

    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

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Sometimes Outlook might have problems sending a message. There may be various causes- corrupted PST, AV scanner etc. The message, instead of going to the Sent Items folder, sits in the Outbox indefinitely. To remove it you can use a free tool cal…
    Not sure what the best email signature size is? Are you worried about email signature image size? Follow this best practice guide.
    This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    737 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

    20 Experts available now in Live!

    Get 1:1 Help Now