VBAWannabee2
New Member
- Joined
- Feb 23, 2022
- Messages
- 28
- Office Version
- 2010
- Platform
- Windows
Hi! Hoping everyone is doing well.
I'm in need of help on my mini-code.
I'm trying to work on sending a set/range of cells from A:F as picture in Outlook and it works.
However, it won't stop generating an email even though the next line/row doesn't have a value. (It was filtered by Column I)
Also, how can it capture the header plus the details? (see picture 2 for reference)
Any help will do, thanks and cheers!
I'm in need of help on my mini-code.
I'm trying to work on sending a set/range of cells from A:F as picture in Outlook and it works.
However, it won't stop generating an email even though the next line/row doesn't have a value. (It was filtered by Column I)
Also, how can it capture the header plus the details? (see picture 2 for reference)
Any help will do, thanks and cheers!
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("2022")
' email recipient
For Each cell In sh.Columns("H").cells.SpecialCells(xlCellTypeVisible)
Set rng = sh.cells(cell.Row, 1).Range("A1:F1").SpecialCells(xlCellTypeVisible)
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
