Sending an Outlook Email Message from Access Database

[Shortstuff12345]

I have a database that is tracking drawing markups from our manufacturing shop. The form I use captures the drawing number, the shop order, purpose of change, description of markup and who needs to make the change. I'd like to add a button so that I can send an email to the person responsible for the drawing and I want to include the drawing number, shop order, purpose of change and description in the body of the email.

For the responsible person, the list of individuals comes from a separate table where the person's name is in one column and their internal ID is in the second column. I'd like to have the email automatically select the person's internal ID from the table and to put that as the TO: portion of the email.

Unfortunately, I have no idea how to even start to create the code to send the email. Can someone help?

[BradHodge]

I have a database that is tracking drawing markups from our manufacturing shop. The form I use captures the drawing number, the shop order, purpose of change, description of markup and who needs to make the change. I'd like to add a button so that I can send an email to the person responsible for the drawing and I want to include the drawing number, shop order, purpose of change and description in the body of the email.

For the responsible person, the list of individuals comes from a separate table where the person's name is in one column and their internal ID is in the second column. I'd like to have the email automatically select the person's internal ID from the table and to put that as the TO: portion of the email.

Unfortunately, I have no idea how to even start to create the code to send the email. Can someone help?

You might try these two links and see if this gets you started. Link 1 Link 2

Hope that helps,
Brad.

[Shortstuff12345]

Brad,

Thanks for the links. The second one was very helpful. I do have a few questions about customizing it though.

First - when I use the code pretty much as-is, I get a message window from Outlook saying that a program is tyring to access email addresses stored in Outlook. I have to click Yes three times in order for the box to disappear and for the message to open. Is there any way to eliminate the warning?

Second - When I use the code as-is for adding a recipient and the CC recipient, the CC recipient overwrites the To recipient.

Set ToContact = .Recipients.Add ("[e-mail]")
ToContact.Type=olCC
Set ToContact = .Recipients.Add("[e-mail]")

Third - How do I specify certain fields from the form to be used for the email addresses and for the body of the email?

Thanks,
Sarah

[Shortstuff12345]

I've been able to add a single field from my form to the body of my email using the following code:

.body  = "Purpose of Change:  " & Me.purpose_change & vbNewLine 

However, I also want to display the markup ID and the markup description as separate paragraphs. I've tried the following:

.body = "Purpose of Change:  " & Me.purpose_change & vbNewLine 
& vbNewline & "Markup Description:  " & Me.markup_description

The code fails at the "&" before "Markup Description: ". Any ideas on how to get the different fields to show as separate paragraphs in the body of the email?

Ultimately, for the To: field on the email, I'd like to pull recipients from three potential fields (Engr_resp, Design_resp, DCI_resp) and to ignore any Null entries, but I haven't been able to get it to work either.

[BradHodge]

I've been able to add a single field from my form to the body of my email using the following code:

.body  = "Purpose of Change:  " & Me.purpose_change & vbNewLine 

However, I also want to display the markup ID and the markup description as separate paragraphs. I've tried the following:

.body = "Purpose of Change:  " & Me.purpose_change & vbNewLine 
& vbNewline & "Markup Description:  " & Me.markup_description

The code fails at the "&" before "Markup Description: ". Any ideas on how to get the different fields to show as separate paragraphs in the body of the email?

Ultimately, for the To: field on the email, I'd like to pull recipients from three potential fields (Engr_resp, Design_resp, DCI_resp) and to ignore any Null entries, but I haven't been able to get it to work either.

Sarah,

Unfortunately I don't have Outlook set up on my work PC. I'll take a look at this more when I get home.

Brad.

[BradHodge]

I've been able to add a single field from my form to the body of my email using the following code:

.body  = "Purpose of Change:  " & Me.purpose_change & vbNewLine 

However, I also want to display the markup ID and the markup description as separate paragraphs. I've tried the following:

.body = "Purpose of Change:  " & Me.purpose_change & vbNewLine 
& vbNewline & "Markup Description:  " & Me.markup_description

The code fails at the "&" before "Markup Description: ". Any ideas on how to get the different fields to show as separate paragraphs in the body of the email?

Ultimately, for the To: field on the email, I'd like to pull recipients from three potential fields (Engr_resp, Design_resp, DCI_resp) and to ignore any Null entries, but I haven't been able to get it to work either.

Sarah,

Here's what I've found so far.

.body = "Purpose of Change:  " & Me.purpose_change & vbNewLine 
& vbNewline & "Markup Description:  " & Me.markup_description

You just need a minor change for this...

 .body= "Purpose of Change: " & Me.purpose_change & vbNewLine & _
"Markup Description: " & Me.markup_description

In other words, use your vbNewLine followed by "& _" and then go to next line with your next field.

As far as the security messages that Outlook makes you say yes to, there are several downloads that you can get that will stop that. I downloaded one called ClickYes Express that sits in your system tray and you can turn it off or on. Apparently ClickYes Pro allows you to set options, and would likely be a little bit more secure while still allowing you to stop the warnings in this situation.

The CC overwriting the To can be solved by putting the ToContact.Type=olCC line after your CC address.

Hope this helps!

Brad.

[Shortstuff12345]

Brad,

I was able to get the body section working properly with the extra & _. However I still can't seem to get the addresses to work properly. Here is the code I have:

Set ToContact = .Recipients.Add(Me.DCI_resp)
Set ToContact = .Recipients.Add(Me.engr_resp)
ToContact.Type = olCC

It pulls in the first address just fine (and I've tried using the three different fields that I want to use - DCI_resp, engr_resp, and design_resp) and they all pull in fine. Unfortunately, the CC field doesn't fill-in now. It doesn't give me any errors (unless the field is blank), but it just doesn't fill-in the CC field.

Any suggestions on how to get it to work? Also, is there a way to get all the fields to go into the TO: box on the email?

Right now, the code crashes if there isn't an entry into one of the fields for the addresses, is there a way to handle blank entries so that the code just skips it?

Thanks,
Sarah

[MMcCarthy]

Try changing the code to this ...

Set ToContact = .Recipients.Add(Me.DCI_resp)
ToContact.Type = olTo
Set ToContact = Nothing
 
Set ToContact = .Recipients.Add(Me.engr_resp)
ToContact.Type = olCC
Set ToContact = Nothing
 

You have to reset ToContact to nothing each time or it can cause problems

[BradHodge]

Try changing the code to this ...

Set ToContact = .Recipients.Add(Me.DCI_resp)
ToContact.Type = olTo
Set ToContact = Nothing
 
Set ToContact = .Recipients.Add(Me.engr_resp)
ToContact.Type = olCC
Set ToContact = Nothing
 

You have to reset ToContact to nothing each time or it can cause problems

Thanks Mary for the help. Hope all works well for you Sarah!

Brad.

[JustJim]

I won't commit the sin of cross-posting, but Adezii and I were just discussing a slightly different aspect of e-mail and outlook over on this thread, and I was wondering anyone on this one had any ideas.

Ho Ho Ho

Jim