Shepherdguy
New Member
- Joined
- Dec 15, 2021
- Messages
- 1
- Office Version
- 365
- Platform
- 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
Last edited by a moderator: