Access Emails

[Steven19]

Hello Access Wizards

I have a table which contains contact emails, but this table is not related to any other table in my database. In fact it is a stand alone table for the moment.



I have about 4 access generated auto emails that are sent with the click of a button.

However in my to field in the VBA I have added the email addresses manually.

Sample of code - works no issues:

Private Sub emailaccounts_Click()
Dim Msg As String


Msg = "Hello,<P>" & _
"<P>" & _
"<ol><P>" & _
"<P>" & _
"<li>Some Data</li><P>" & _
"</ol><P>" & _
"<P>" & _
"Kind Regards"


Dim O As Outlook.Application
Dim M As Outlook.MailItem


Set O = New Outlook.Application
Set M = O.CreateItem(olMailItem)


With M
        .BodyFormat = olFormatHTML
        .HTMLBody = Msg
        .To = "someone@mycompany.com"
        .CC = "someoneelse@myothercompany.com"
        .Subject = "New Financial Review for " & [CompanyName]
        .Display
End With


Set M = Nothing
Set O = Nothing
End Sub

I create these emails and can call fields from the form ad have them in my email no problem, but want to make it so that me email address can by dynamic, therefore if someone@somecompany.com leaves, I just update the email table and don't have to latter dive into each email button to change the email address.

I can change the email when the email displays, but would like the option not to.

Any ideas?

Regards

[pbaldy]

One option is using DLookup() to get the address(es). If the table has a single record you wouldn't need a criteria, otherwise you would:

http://www.theaccessweb.com/general/gen0018.htm

[Steven19]

Good afternoon

Thank you, this was an excellent fix.

Regards

[pbaldy]

Happy to help!