VBAWannabee2
New Member
- Joined
- Feb 23, 2022
- Messages
- 28
- Office Version
- 2010
- Platform
- Windows
Hi! I'm figuring out ways on how to auto-create an email with a picture/table in it. (range of cells in excel)
Objective: Auto-email with picture in a bodymail.
Issue/Error: The line after the list were also included in the creation of email but it shouldn't because it's just a blank cell.
This is my code, I'm just restructuring some codes from my previous work.
Also see attached picture of the email. (Image 1 = issue/errorr, Image 2 = list, Image 3 = objective)
Any help will do! Thanks!!
Objective: Auto-email with picture in a bodymail.
Issue/Error: The line after the list were also included in the creation of email but it shouldn't because it's just a blank cell.
This is my code, I'm just restructuring some codes from my previous work.
Also see attached picture of the email. (Image 1 = issue/errorr, Image 2 = list, Image 3 = objective)
Any help will do! Thanks!!
VBA Code:
Sub z()
Dim OutApp As Object
Dim Outmail As Object
Dim cell As Range
Dim rng As Range
Dim table As Range
Dim pic As Picture
Dim sh As Worksheet
Dim wordDoc
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
Set sh = Sheets("Sheet1")
' email recipient
For Each cell In sh.Columns("H").cells.SpecialCells(xlCellTypeConstants)
Set rng = sh.cells(cell.Row, 1).Range("A1:G1").Offset(1, 0)
Set OutApp = CreateObject("Outlook.Application")
Set Outmail = OutApp.CreateItem(0)
Set table = rng
sh.Activate
table.Copy
Set pic = sh.Pictures.Paste
pic.Cut
strbody = "<p>Test Email</p>"
With Outmail
.To = cell.Value
.Subject = "Test"
.Display
Set wordDoc = Outmail.GetInspector.WordEditor
With wordDoc.Range
.InsertParagraphAfter
.PasteAndFormat wdChartPicture
.InsertParagraphAfter
End With
.HTMLBody = "<BODY style = font-size:10.5pt; font-family;Arial>" & _
strbody & .HTMLBody
End With
Next cell
MsgBox "Process complete!"
Set OutApp = Nothing
Set Outmail = Nothing
End Sub
