Use table email addresses for Outlook Email

[Kenny G]

Hello,

I have a table "tblNonCompIPOPAllStandards" this table has the email
addresses for an outlook (email) that I want to send an attached snapshot.

the table has fields appropriately named "To" "CC" "BCC"

This time the table - four records (a result of a make table) has two
addressees listed for the To address, one addressee listed for the CC address
and one addressee listed for the BCC on the email.

Putting together the one email would cycle through all the four records
capture all addressees under their respective To, CC and BCC areas.

The code below is in a module called modEmailReport. It probably looks
rather familiar but I need the code to not "hard code" the names of
recipients but the names from my table.

Sub sbSendMessage(Optional AttachmentPath)
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment

On Error GoTo ErrorMsgs

' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")
' Create the message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
With objOutlookMsg
' Add the To recipient(s) to the message. Substitute
' your names here.
Set objOutlookRecip = .Recipients.Add("Nancy Davolio")
objOutlookRecip.Type = olTo
' Add the CC recipient(s) to the message.
Set objOutlookRecip = .Recipients.Add("Andrew Fuller")
objOutlookRecip.Type = olCC
' Set the Subject, Body, and Importance of the message.
.Subject = "This is an Automation test with Microsoft Outlook"
.Body = "Last test." & vbCrLf & vbCrLf
.Importance = olImportanceHigh 'High importance
' Add attachments to the message.
If Not IsMissing(AttachmentPath) Then
Set objOutlookAttach = .Attachments.Add(AttachmentPath)
End If
' Resolve each Recipient's name.
For Each objOutlookRecip In .Recipients
If Not objOutlookRecip.Resolve Then
objOutlookMsg.Display
..........End If
End If
Next
.Send
End With
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
Set objOutlookRecip = Nothing
Set objOutlookAttach = Nothing
ErrorMsgs:
If Err.Number = "287" Then
MsgBox "You clicked No to the Outlook security warning. " & _
"Rerun the procedure and click Yes to access e-mail" & _
"addresses to send your message. For more information, & _
"see the document at http://www.microsoft.com/office" & _
"/previous/outlook/downloads/security.asp. " "
Else
Msgbox Err.Number, Err.Description
End If
End Sub

Thanks for your assistance.

 

[Pete D.]

See inline

Hello,

I have a table "tblNonCompIPOPAllStandards" this table has the email
addresses for an outlook (email) that I want to send an attached snapshot.

the table has fields appropriately named "To" "CC" "BCC"

This time the table - four records (a result of a make table) has two
addressees listed for the To address, one addressee listed for the CC
address
and one addressee listed for the BCC on the email.

Putting together the one email would cycle through all the four records
capture all addressees under their respective To, CC and BCC areas.

The code below is in a module called modEmailReport. It probably looks
rather familiar but I need the code to not "hard code" the names of
recipients but the names from my table.

Sub sbSendMessage(Optional AttachmentPath)
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment

On Error GoTo ErrorMsgs

' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")
' Create the message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
With objOutlookMsg
' Add the To recipient(s) to the message. Substitute
' your names here.
Set objOutlookRecip = .Recipients.Add("Nancy Davolio")
objOutlookRecip.Type = olTo
' Add the CC recipient(s) to the message.
Set objOutlookRecip = .Recipients.Add("Andrew Fuller")

'Add the To recipient(s) to the message.
Set objOutlookRecip = .Recipients.Add(fldSendtoWho)
objOutlookRecip.Type = olTo
same idea for the rest

 

[Kenny G]

Pete,

I appreciate your input. I could replace each manually but I want to pull
the addresses out utilizing automation.

I am trying to loop through the To field of the table to populate the To
address in the email. The CC and BCC will be done in the same manner.

 

[Pete D.]

I assume you want to send one email to each individiual in each record. So
if your query produces 10 records each with a TO: CC: and BCC: that would be
10 email with three addresses on each. First build your query then you will
use that as the recordset for your code. You then will loop through each
record. This tread has a zip file with sample datafile showing these
methods using forms to set up the query. It also uses the sendobject but
you can easily adapt it to the code you have already.

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=59311