troubleshooting Question

excel vba -send email from excel to outlook

Avatar of drtopserv
drtopservFlag for Israel asked on
Microsoft OfficeOutlookMicrosoft ExcelVBA
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
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 8 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 8 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros