Help with emails!

Charlottew14 · Dec 3, 2012

Hello,

I'm a total novice when it comes to using code (and access generally!) and am having problems setting up my database to send emails, using access 2010.

I have a form which assigns tasks to different people, with a button to send an email alert when a new task has been assigned. The code I'm using to send the email is:

Dim olApp As Object
Dim objMail As Object

On Error Resume Next 'Keep going if there is an error

Set olApp = GetObject(, "Outlook.Application") 'See if Outlook is open

If Err Then 'Outlook is not open
Set olApp = CreateObject("Outlook.Application") 'Create a new instance of Outlook
End If

'Create e-mail item
Set objMail = olApp.CreateItem(olMailItem)

With objMail

'Set body format to HTML
.BodyFormat = olFormatHTML
.To = "name@company.com"
.Subject = "Task Assigned"
.HTMLBody = "Text"
.send

End With

MsgBox "Operation completed successfully"

which works fine, but i'm struggling to make the ".To = " line pick up an email address. Because they want to go to different people each time, I don't want to just have the one email address (as in the code above). I have a table with the email addresses - how can I ask it to pick up the email address depending on who the task has been assigned to?

Hope that makes sense.

Many thanks in advance!!

Alansidman · Dec 3, 2012

On your form, add a combo box that lists all the potential email recipients.

Use this control (combo box) as a variable in your code, then you can assign that value to your .To line of code.

Alan

Charlottew14 · Dec 3, 2012

Sorry, like I said I'm pretty much useless when it comes to using VBA! How would I go about that?

Thanks!

Alansidman · Dec 3, 2012

See my insertions in Blue

Code:

Dim olApp As Object
Dim objMail As Object


On Error Resume Next 'Keep going if there is an error


Set olApp = GetObject(, "Outlook.Application") 'See if Outlook is open


If Err Then 'Outlook is not open
Set olApp = CreateObject("Outlook.Application") 'Create a new instance of Outlook
End If

'Create e-mail item
Set objMail = olApp.CreateItem(olMailItem)

With objMail

'Set body format to HTML
.BodyFormat = olFormatHTML
.To = [COLOR="navy"]me.cmbYourComboBox.Value[/COLOR]
.Subject = "Task Assigned"
.HTMLBody = "Text"
.send

End With

MsgBox "Operation completed successfully"

Charlottew14 · Dec 3, 2012

Hi,

I've made my combo box so that there are two columns in it - the username, which is the one you see on the from, and the email address which you see when you click on the arrow to select, but doesn't appear on the form - how can I tell the code to look at the second column?

If I can do it this way, it means that the user only has to select one box, instead of two.

Thanks!!!

Alansidman · Dec 3, 2012

Change my line of code to read:

Code:

.To = me.cmbYourComboBox.Columns(1).Value

This assumes that the email address is in the second column of the query underlying the combo box.

Access counts the columns from left to right beginning with zero.

FYI http://www.baldyweb.com/Autofill.htm

Charlottew14 · Dec 4, 2012

Thank you so much for your help, it works perfectly

I do have another question though - is it possible to select a text box to be added to the content of the email - eg. a description of the task that has been assigned??

Alansidman · Dec 4, 2012

You could put a new text box on your form
In your code change this line
.Subject = "Task Assigned"

to read
.Subject = "Task Assigned" & me.YourTextBoxName.Value

This should append the specific task into the subject for the email.

Charlottew14 · Dec 7, 2012

Is there any way to add this to the body of the email, rather than the subject?

Many Thanks

Alansidman · Dec 7, 2012

I have not done this before and this is untested, but try this:

.HTMLBody = me.YourTextBoxName.Value & "Text"

Alan

Help with emails!

Charlottew14

Registered User.

Alansidman

AWF VIP

Charlottew14

Registered User.

Alansidman

AWF VIP

Charlottew14

Registered User.

Alansidman

AWF VIP

Charlottew14

Registered User.

Alansidman

AWF VIP

Charlottew14

Registered User.

Alansidman

AWF VIP

Similar threads

Users who are viewing this thread