How to send mass emails of an excel table to multiple recipients on outlook. problem: the content of the table will be different depending on the reci

[yhashem]

Hello,

The task I am assigned is to find a way to automate the task of sending daily emails to a list of recipients. the email contains one sentence and one table. the twist is that the table is the same for all but the contents of the table should be customized depending on the recipients.

I am not sure how to approach this problem. Here is the context:

the emails we are sending are to place orders for a product (the same product for all recipients)
The recipients of these emails are different companies that place orders on our behalf.
The table has 3 column and is the same for all recipients
HOWEVER, the contents of the table will be different depending on the recipients.

I am a beginner at VBA and this is my first post, any suggestions would be much appreciated.

 

[daverunt]

Hi,

more detail is required if you want some ideas on how to approach the problem.

The following example is a simplified bit of code for sending a single range from a worksheet in a mail using RangeToHTML
Mail Range/Selection in the body of the mail
If you knock up a table in Sheet1 - E3:G8 and copy/paste the code into a module you can run the macro to display a mail with the table.

You could add a loop to this so that it sends the same range to a list of recipients in Column A for example.

Some information that might help someone to help you.
Where is the list of recipients stored?
How do you differentiate between the customers and the content they are to receive?
How is that different information stored? Do you have separate tables on separate sheets etc.
Is there any different text in the sentence or is that the same?

Sub SendTable()
' Don't forget to copy the function RangetoHTML in the module.

Dim rng As Range


Dim OutApp As Object
Dim OutMail As Object

'Range of table
Set rng = Worksheets("Sheet1").Range("E3:G8")

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

With OutMail
.To = "Can use specific string, or range"
.CC = "Can use specific string, or range"
.BCC = "Can use specific string, or range"
.Subject = "Can use specific string, or range"
.HTMLBody = RangetoHTML(rng)
'.Send  'comment out display and uncomment out send when it works as required
.display ' use for testing
End With


Set OutMail = Nothing
Set OutApp = Nothing
End Sub

Function RangetoHTML(rng As Range)
' Changed by Ron de Bruin 28-Oct-2006
' Working in Office 2000-2010
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"

'Copy the range and create a new workbook to past the data in
rng.Copy
Set TempWB = Workbooks.Add(1)
With TempWB.Sheets(1)
.Cells(1).PasteSpecial Paste:=8
.Cells(1).PasteSpecial xlPasteValues
.Cells(1).PasteSpecial xlPasteFormats
.Cells(1).Select
Application.CutCopyMode = False
On Error Resume Next
.DrawingObjects.Visible = True
.DrawingObjects.Delete
On Error GoTo 0
End With

'Publish the sheet to a htm file
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

'Read all data from the htm file into RangetoHTML
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=")

'Close TempWB
TempWB.Close savechanges:=False

'Delete the htm file we used in this function
Kill TempFile

Set ts = Nothing
Set fso = Nothing
Set TempWB = Nothing
End Function