Adding Text String and Image to email from Excel

Shepherdguy

New Member
Joined
Dec 15, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
I am trying to automate an email that will send the daily Heat Advisory (working out the bugs before it gets hot again). I am a newbie to VBa codes and I am trying to absorb what I can but it is beating me. Here is the code I currently have set up. I will also add a photo of the value it returns: The two issues I have is that I want the Spanish to be a separate paragraph. Also, the "Day of the week" is not converting to Spanish.

VBA Code:
Sub sendMail()

    Dim xOutApp As Object
    Dim xOutMail As Object
    Dim xHTMLBody As String
    Dim xRg As Range
    On Error Resume Next
    'Set xRg = Application.InputBox("Please select the data range:", "KuTools for Excel", Selection.Address, , , , , 8)
    Set xRg = Range("A1:L34")
    If xRg Is Nothing Then Exit Sub
    With Application
        .Calculation = xlManual
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    Set xOutApp = CreateObject("outlook.application")
    Set xOutMail = xOutApp.CreateItem(olMailItem)
    Call createJpg(ActiveSheet.Name, xRg.Address, "HeatAdvisory")
    TempFilePath = Environ$("temp") & "\"
    xHTMLBody = "<span LANG=EN>" _
            & "<p class=style2><span LANG=EN><font FACE=Calibri SIZE=3>" _
            & "Here is the Heat Advisory for " & Format(Date, "dddd ") & Format(Date, "mm/dd/yyyy.") & " Please ensure employees are drinking plenty of water throughout the workday. Breaks should be taken in cool shaded areas. Sunscreen is highly recommended if working outdoors." _
        & vbNewLine & vbNewLine & "Aquí está el aviso de calor para " & Format(Date, "[$-000a]dddd ") & Format(Date, "dd/mm/yyyy.") & " Asegúrese de que los empleados beban mucha agua durante la jornada laboral. Los descansos deben tomarse en áreas frescas y sombreadas. Se recomienda usar protector solar si se trabaja al aire libre." _
            & "<br>" _
            & "<img src='cid:HeatAdvisory.jpg'>" _
            & "<br></font></span>"
    With xOutMail
        '.Subject = ""
        .HTMLBody = xHTMLBody
        .Attachments.Add TempFilePath & "HeatAdvisory.jpg", olByValue
        .To = "[EMAIL]John.Doe@TestMail.com[/EMAIL]"
        .Subject = "Heat Advisory -    degrees"
        .Body = MultiLine(rngBody)
        .Cc = " "
        .Display
    End With
End Sub

Sub createJpg(SheetName As String, xRgAddrss As String, nameFile As String)
    Dim xRgPic As Range
    Dim xShape As Shape
    ThisWorkbook.Activate
    Worksheets(SheetName).Activate
    Set xRgPic = ThisWorkbook.Worksheets(SheetName).Range(xRgAddrss)
    xRgPic.CopyPicture
    With ThisWorkbook.Worksheets(SheetName).ChartObjects.Add(xRgPic.Left, xRgPic.Top, xRgPic.Width, xRgPic.Height)
        .Activate
        For Each xShape In ActiveSheet.Shapes
            xShape.Line.Visible = msoFalse
        Next
        .Chart.Paste
        .Chart.Export Environ$("temp") & "\" & nameFile & ".jpg", "JPG"
    End With
   Worksheets(SheetName).ChartObjects(Worksheets(SheetName).ChartObjects.Count).Delete
Set xRgPic = Nothing
End Sub
 

Attachments

  • Test.jpg
    Test.jpg
    18.8 KB · Views: 29
Last edited by a moderator:

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Forum statistics

Threads
1,224,815
Messages
6,181,136
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top