Hello everyone! I'm new to the community, but have been lurking for a while. Here's my current issue, that I would like a little input on.
I'm attempting to set up a macro coupled with an Access VBA module. Once you click a button that says "Email Reports", it will call up six different emails, all with different distribution lists and attachments. Right now it works, but I am having a hard time specifying to Access what (and where) my distribution list is (which is in Outlook), instead of singular emails.
Of course, it will work if I type in every single email, but I want it to reference the list so the code isn't hundreds of lines long, and I can edit the lists without digging through code to remove or add an email every once in a while. This is what it looks like now:
Dim objOutlook2 As New Outlook.Application
Dim objMessage5 As MailItem, strInspSupOpen As String ' outlook mail message
Dim objMessage6 As MailItem, strInspOpen As String ' outlook mail message
strInspSupOpen = "C:\Users\Me\Documents\Reports\Attachment1.xps"
strInspOpen = "C:\Users\Me\Documents\Reports\Attachment2.xps"
Set objMessage2 = objOutlook2.CreateItem(olMailItem)
With objMessage2
.To = (e-mail address removed) (e-mail address removed)
.Subject = "Daily/Programs/Inspection/Update"
.Body = "Good morning everyone, here are the needed reports. "
.Attachments.Add strInspOpen
.Attachments.Add strInspSupOpen
.Display ' so I can review it before sending it
End With
Set objOutlook2 = Nothing
Set objMessage2 = Nothing
It's very easy to specify each individual email, but how can I tell it "send to THIS contact (which happens to be a distribution list, unbeknownst to the program)"? I could specify the distribution list within a table, but then I need to maintain the two in Access and Outlook separately, which I wish not to do.
Thank you so much for your help! And if I figure it out, i'll post back.
I'm attempting to set up a macro coupled with an Access VBA module. Once you click a button that says "Email Reports", it will call up six different emails, all with different distribution lists and attachments. Right now it works, but I am having a hard time specifying to Access what (and where) my distribution list is (which is in Outlook), instead of singular emails.
Of course, it will work if I type in every single email, but I want it to reference the list so the code isn't hundreds of lines long, and I can edit the lists without digging through code to remove or add an email every once in a while. This is what it looks like now:
Dim objOutlook2 As New Outlook.Application
Dim objMessage5 As MailItem, strInspSupOpen As String ' outlook mail message
Dim objMessage6 As MailItem, strInspOpen As String ' outlook mail message
strInspSupOpen = "C:\Users\Me\Documents\Reports\Attachment1.xps"
strInspOpen = "C:\Users\Me\Documents\Reports\Attachment2.xps"
Set objMessage2 = objOutlook2.CreateItem(olMailItem)
With objMessage2
.To = (e-mail address removed) (e-mail address removed)
.Subject = "Daily/Programs/Inspection/Update"
.Body = "Good morning everyone, here are the needed reports. "
.Attachments.Add strInspOpen
.Attachments.Add strInspSupOpen
.Display ' so I can review it before sending it
End With
Set objOutlook2 = Nothing
Set objMessage2 = Nothing
It's very easy to specify each individual email, but how can I tell it "send to THIS contact (which happens to be a distribution list, unbeknownst to the program)"? I could specify the distribution list within a table, but then I need to maintain the two in Access and Outlook separately, which I wish not to do.
Thank you so much for your help! And if I figure it out, i'll post back.
Last edited: