Sending out emails to different users with different information based on a table

[kkernohan]

Hi All,

I have a question regarding how to send out emails either automatically or through the click of a button to multiple people based on a table. The table is in the format...


<TABLE style="WIDTH: 192pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=256><COLGROUP><COL style="WIDTH: 48pt" span=4 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: silver; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl64 height=20 width=64>User Name</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: silver; WIDTH: 48pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl64 width=64>Report Type</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: silver; WIDTH: 48pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl64 width=64>Actions in Last 7 Calender Days</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black; BACKGROUND-COLOR: silver; WIDTH: 48pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl64 width=64>Actions in Last 30 Calender Days</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: silver 0.5pt solid" class=xl65 height=20 width=64>Smith, T</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: silver 0.5pt solid" class=xl65 width=64>Co-Sign</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: silver 0.5pt solid" class=xl66 width=64>0</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: silver 0.5pt solid" class=xl66 width=64>1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl65 height=20 width=64>Smith, T</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl65 width=64>Decision</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl66 width=64>54</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl66 width=64>184</TD></TR><TR style="HEIGHT: 30pt" height=40><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 30pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl65 height=40 width=64>Smith, T</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl65 width=64>UW Handling</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl66 width=64>21</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl66 width=64>67</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl65 height=20 width=64>Jones, M</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl65 width=64>Co-Sign</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl66 width=64>0</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl66 width=64>5</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl65 height=20 width=64>Jones, M</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl65 width=64>Decision</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl66 width=64>5</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl66 width=64>11</TD></TR><TR style="HEIGHT: 30pt" height=40><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 30pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl65 height=40 width=64>Jones, M</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl65 width=64>UW Handling</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl66 width=64>0</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl66 width=64>7</TD></TR><TR style="HEIGHT: 30pt" height=40><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 30pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl65 height=40 width=64>Lawson, T</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl65 width=64>Admin Handling</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl66 width=64>169</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl66 width=64>669</TD></TR><TR style="HEIGHT: 30pt" height=40><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 30pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl65 height=40 width=64>Lawson, T</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl65 width=64>Decision</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl66 width=64>0</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl66 width=64>4</TD></TR></TBODY></TABLE>

etc...


So what I would like to do is to send an email to each person in this table with the information that corresponds to them in the table (seems simple enough im just not an Access pro)

So an example of an email would be...

This would go to the email address corresponding to Smith, T (I can add this information into the table if need be)

Smith, T
last 7 days last 30 days
Co-sign - 0 1
Decision - 54 184
UW Handling - 21 67

This would go to the email address corresponding to Jones, M (once again I can add this information into the table)

Jones, M
last 7 days last 30 days
Co-sign - 0 5
Decision - 5 11
UW Handling - 0 7

etc. (for everyone in the table)


Thank you to anyone who can provide some help. I have taken a look everywhere online and I cant seem to find any information on how to do this.

 

[Montez659]

Below is a slice of code to at least get you started, and this will send email through Outlook. I would recommend putting Outlook into Offline mode while you play around with this (make sure you turn it on again!) and any emails that will be sent will just stack up in your Outbox.

A couple words of caution for you. It looks like the way your data is stored in your table is incorrect. What I mean is you have duplicate info in the user name and report type columns. Basically, you would want to create a junction table with one table containing the user name, another table with report type (both of these would have there own Primary Keys) and a third (Junction) that uses the PK from the other two tables as foreign keys and stores the extra info. I know this is a lot of info and may be somewhat confusing, but i would highly recommend taking a look at http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx especially the Normalization part.

Dim emailSubject
Dim emailTo
Dim emailBody

Set Mail_Object = CreateObject("Outlook.Application")
With Mail_Object.CreateItem(o)
    .Subject = emailSubject
    .To = emailTo
    .Body = emailBody
    '.Attachments.Add TempFilePath & TempFileName & FileExtStr 'add error trapping
    .Send
End With

 

[jasmith4]

First, you should definitely add an Email-address column.

In 2003 pull down Tools / Office Links / Merge with Word; in 2010 the External Data ribbon has a Word Merge tool. Both lead to a Word wizard. If you don't have anything set up, choose to create a new document, and then here's what you should do at each step:

1. Choose Email messages
2. Use the current document
3. Skip to next step: it should say [table/query] in "database" already.
4. Type in text and use Insert Merge Field to add database information.
5. (preview) make sure all is well -- if not, go back to 4.
6. Click Electronic Mail and on the Merge to Email dialog box's To: drop-down, choose the field that has the Email address. CAREFUL: Emails WILL go out when you click OK!

You can actually skip step 6, but then you should save this Word document. Next time you want to do a Word merge on the same table/query, tell the wizard to use an existing document, and point to this document.