How to automate sending email with different attachments

[Guest]

Firstly, apologies if this turns out not to be a VB question.

I have a situation where a member of my team distributes monthly reports to
approx 400 different recipients and at present this is achieved via printing
and sending in the mail which takes approx 2 full days.

To reduce costs and time taken in this process I would like to automate the
process over email. Basically I would like to perform a function similar to
mail merge in word whereby I can set a listing of names, email addresses and
file names and then run a macro to:
-Create a new email to each address in turn.
-Include a standard subject
-Include a brief passage of text ideally preceeded by recipient name from
the listing.
-Attach an Excel file relevant to that recipient using the listing (for this
I plan to use a common file path each month and maintain the file name).

Any suggestions as to if/how this might be achieved would be very welcome.

Matt

 

[mlafarlett]

in .net...you'll 1st have to add system.web.mail to your references and
'import system.web.mail. The function I use follows:
Public Sub fnSendMail(ByVal psRecipients As String, ByVal psSubject
As String, ByVal psBody As String, ByVal psAttachmentLocation As
String)

Dim Message As System.Web.Mail.MailMessage = New
System.Web.Mail.MailMessage
Message.To = psRecipients
Message.From = "(e-mail address removed)"
Message.Subject = psSubject
Message.Body = psBody
If psAttachmentLocation.Length > 0 Then
Try
Dim objMailAttachment As New
MailAttachment(psAttachmentLocation)
Message.Attachments.Add(objMailAttachment)
Catch ex As Exception
Message.Body &= vbCrLf & "****ERRROR**** " & ex.Message
End Try
End If

Try
SmtpMail.SmtpServer = msSMTPIPAddress
SmtpMail.Send(Message)
Catch ehttp As System.Web.HttpException
Call fnWriteToLog("fnSendMail", "Error notifying support
personnel = " & ehttp.Message & "..." & ehttp.ErrorCode, "warning")
Catch ex As Exception
Call fnWriteToLog("fnSendMail", ex.Message, "warning")
End Try

End Sub

 

[Guest]

Thanks for the reply.

Unfortunately I am by no means an expert. Is it possible to explain in a
little more detail what it is that I need to do?

Many thanks

 

[Tim Mc]

Matt,
I have found that the macro and procedure for using it described by MVP Doug
Robbins in the following link works brilliantly to do excactly what you are
asking. I hope you have as much success with it as I have had.
Regards, Tim Mc
http://word.mvps.org/faqs/mailmerge/MergeWithAttachments.htm

Thanks for the reply.

Unfortunately I am by no means an expert. Is it possible to explain in a
little more detail what it is that I need to do?

Many thanks

in .net...you'll 1st have to add system.web.mail to your references and
'import system.web.mail. The function I use follows:

[quoted text clipped - 29 lines]

End Sub