Email multiple addresses with multiple rows with VBA Macro

atditiljazi

New Member
Joined
Nov 22, 2022
Messages
41
Office Version
  1. 365
Platform
  1. Windows
hi, i am hoping someone could help me adjusting a macro i found or provide a new one?

i need to send multiple emails with multiple rows as an attachment the the relevant addresses. i ideally would like the email addresses to be in a separate sheet and it would be linked to the supplier codes in the main sheet. the program i export the file doesn't supply the email address so linking the supplier code with the email address is the only way of doing it, i beli

table below


first namesecond nameemail address
aaaa@email.com
bbbb@email.com
table below of example.



weekorder datecostexpedite/de-expeditedescription origincommentsdue datesupplier codecompany
110/01/2277treeuk03/03/2235432a
222/01/225logsuk
03/03/22
454543a
110/01/2242sofauk02/04/22654645b
222/01/2232tvuk29/01/22453534c
110/01/2212tvuk02/02/2232432432c

here is the macro i found which needs adjusting and i just don't have no clue where to start because to be honest I don't have a clue about VBA. help will be very appreciated!!!

Sub CreateEmails()
Dim OutApp As Object, OutMail As Object, v As Variant, i As Long, rng As Range
v = Range("A2", Range("A" & Rows.Count).End(xlUp)).Resize(, 2).Value
Set OutApp = CreateObject("Outlook.Application")
With CreateObject("scripting.dictionary")
For i = LBound(v) To UBound(v) 'loops through rows
If Not .exists(v(i, 1)) Then
.Add v(i, 1), Nothing
With ActiveSheet
.Range("A1").AutoFilter 1, v(i, 1)
Set rng = .AutoFilter.Range
Set OutMail = OutApp.CreateItem(0)
With OutMail
.To = v(i, 2)
.Subject = "This is a test message"
.HTMLBody = RangetoHTML(rng)
.Display
End With
End With
End If
Next i
ActiveSheet.Range("A1").AutoFilter
End With
End Sub

Function RangetoHTML(rng As Range)
Dim fso As Object
Dim ts As Object
Dim TempFile As String
Dim TempWB As Workbook
TempFile = Environ$("temp") & "\" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"
rng.Copy
Set TempWB = Workbooks.Add(1)
With TempWB.Sheets(1)
.Cells(1).PasteSpecial Paste:=8
.Cells(1).PasteSpecial xlPasteValues, , False, False
.Cells(1).PasteSpecial xlPasteFormats, , False, False
.Cells(1).Select
Application.CutCopyMode = False
On Error Resume Next
.DrawingObjects.Visible = True
.DrawingObjects.Delete
On Error GoTo 0
End With
With TempWB.PublishObjects.Add( _
SourceType:=xlSourceRange, _
Filename:=TempFile, _
Sheet:=TempWB.Sheets(1).Name, _
Source:=TempWB.Sheets(1).UsedRange.Address, _
HtmlType:=xlHtmlStatic)
.Publish (True)
End With
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
RangetoHTML = ts.readall
ts.Close
RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", "align=left x:publishsource=")
TempWB.Close savechanges:=False
Kill TempFile
Set ts = Nothing
Set fso = Nothing
Set TempWB = Nothing
End Function
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

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