Create individualized employee email messages

[ker_01]

We have a vendor who manages the web-based presentation of some required
compliance training. To improve the process, we would like to send out an
individualized URL to every employee (probably using their Employee ID in the
URL) instead of the current process, which requires each employee to click a
generic URL and log in.

Other than the obvious (a VBA loop through an array of email addresses and
Employee IDs, probably loaded from an Excel file) are there any other tips,
tricks, or gotchas associated with this type of bulk internal email
generation? All emails will only be to our internal employees, but we do have
several domains and two different exchange servers. I anticipate that we
would generate 100% of these emails from one location (one computer, which is
on Exchange Server 1), just for convenience.

Example:

Group 1/ Exchange Server 1: @companyUSA.com
: @companyCorp.com

: @companyservice.com
Group 2/ Exchange Server 2: @companyEMEA.com

I'd be more likely to do this from Outlook2007, but could use Outlook2003 if
there was a good reason to do so (if any relevant features have been
depreciated).

I'm be inclined to do this in Outlook (despite my greater familiarity with
Excel VBA) to avoid getting a warning with each email, or having to set it up
with CDO.

I appreciate any thoughts you have; I am interested in feedback on overall
approach or potential problems, I don't think the code itself will be that
much of a problem, but I want to make sure I figure out the best solution
before I start coding for it. If I have code problems I can alway post those
later.

Many thanks,
Keith

 

[Charlize]

Something to tinker with ... First you need to create
distributionlist of email-adresses. In this example the name i
'LijstMailing'. Then we create an email for each person on the list
When using an external excelfile for holding the relevant id for th
person that gets an email, you could be able to create an individua
body for the email. Or export the list from excel to a delimite
textfile. Then we loop through each line of the txt file and put thos
in an array. Later use the array to find the correct email address an
corresponding id.


VBA Code:
--------------------


Sub Send_Mailing()
Dim DList As DistListItem, i As Long
Dim MyMessage As Outlook.MailItem
'Since outlook doesn't have the getopenfilename
'we use the excelobject
Dim oExcel As Object
Dim vFile
Set oExcel = CreateObject("Excel.Application")
'LijstMailing is the name of your distributionlist with all
'the contacts that you want to receive a mail
Set DList = Application.Session.GetDefaultFolder(olFolderContacts). _
Items("LijstMailing")
vFile = oExcel.GetOpenFilename(MultiSelect:=False)
For i = 1 To DList.MemberCount
Set MyMessage = Application.CreateItem(olMailItem)
With MyMessage
.To = DList.GetMember(i).Address
.Subject = "Personal URL Mailing"
.Body = "Here some info regarding your training."
If vFile <> False Then
'Here the code for adding the unique url
.Body = .Body & vbNewLine & "Personal url : ..."
'or change coding to use a delimited textfile with two dimensional array
'instead of just adding an attachment. This part will only be processed
'when you have chosen a file with the e-mails and id's of the participants
.Attachments.Add vFile
End If
.Display
'If you don't want to display the message use .Send instead
'of .Display
'.Send
End With
Next i
'Clear the object
Set oExcel = Nothing
End Sub