troubleshooting Question

excel vba -send email from excel to outlook

Avatar of drtopserv
drtopservFlag for Israel asked on
OutlookVBAMicrosoft ExcelMicrosoft Office
8 Comments1 Solution34 ViewsLast Modified:
I have this code which do the job of sending email to outlook through excel vba ,
I need a help to fix the code to be able to put the value content of range cell in excel and insert them into the "strbody"  variable string.
 for now the code below add string to the body of the email :
strbody = "Dear Customer" & "<br><br>" & _
        "Below you find a picture of your data." & "<br>" & _
        "If you need more information let me know." & "<br><br>" & _
        "Regards Ron<br>"
I need to add this string above + content of the range cells in excel .

Sub Mail_small_Text_And_JPG_Range_Outlook()
    'Ron de Bruin, 25-10-2019
    'This macro use the function named : CopyRangeToJPG
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String
    Dim MakeJPG As String


    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With


    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)


    strbody = "Dear Customer" & "<br><br>" & _
        "Below you find a picture of your data." & "<br>" & _
        "If you need more information let me know." & "<br><br>" & _
        "Regards Ron<br>"
             
    'Create JPG file of the range
    'Only enter the Sheet name and the range address
    MakeJPG = CopyRangeToJPG("day", "c4:z50")


    If MakeJPG = "" Then
        MsgBox "Something go wrong, we can't create the mail"
        With Application
            .EnableEvents = True
            .ScreenUpdating = True
        End With
        Exit Sub
    End If


    On Error Resume Next
    With OutMail
        .To = "TEST@gmail.com"
        .CC = ""
        .BCC = ""
        .Subject = "This is the Subject line"
        .Attachments.Add MakeJPG, 1, 0
        'Note: Change the width and height as needed
        .HTMLBody = "<html><p>" & strbody & "</p><img src=""cid:NamePicture.jpg"" width=750 height=700></html>"
        .Display 'or use .Send
    End With
    On Error GoTo 0


    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With


    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

Open in new window

ASKER CERTIFIED SOLUTION
Log in to continue reading
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform for $9.99/mo
View membership options
Unlock 1 Answer and 8 Comments.
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
The Value of Experts Exchange in My Daily IT Life

Experts Exchange (EE) has become my company's go-to resource to get answers. I've used EE to make decisions, solve problems and even save customers. OutagesIO has been a challenging project and... Keep reading >>

Mike

Owner of Outages.IO
Phoenix, Arizona, United States
Member Since 2016
Join a full scale community that combines the best parts of other tools into one platform.
Unlock 1 Answer and 8 Comments.
View membership options
“All of life is about relationships, and EE has made a virtual community a real community. It lifts everyone's boat.”
William Peck

Member since 2004