Using VBA to send an email.

[jool]

Hello all,

From a form, I have been trying to send a simple email message to an email address specified in a drop down menu on an access form. I was helped greatly by members of a different Access forum yesterday, but unfortunately I never managed to solve my problem (and today people seem to have lost interest in my thread).

So yesterday, I carried out the following steps.

I Created and populated a table with 3 columns - autonumber, name and email address and populated this table.

I created a unbound combo drop down menu, showing only one column (name) and called it emailname.

I then created a form with a button on it. At the data tab of the button's properties I opened up a code builder box.

In between the following:

Private Sub Command2_Click()

End Sub

I pasted the following code:

Dim strToWhom As String
Dim strMsgBody As String
Dim strSubject As String

strSubject = "Your Subject"
strToWhom = Me[COLOR="Red"].emailname[/COLOR].Column(2)
strMsgBody = "Your Body text goes here!"

DoCmd.SendObject , , , strToWhom, , , strSubject, strMsgBody, True

This however brings up a complile error highlighting whichi is highlighted above with the message ' Method or data member not found'.

I've tried changing that name several times but to no joy. VBA is all new to me, so it's likely I'm making a very simple mistake.

Any help would be really appreciated.

Thanks a lot in advance

Jool

 

[dsigner]

The reason for the error message is that you can only send a specified access object with this command. As the object type and object name are blank it cannot find them. You will have to enclose your message in an object. I would pick a form with a single text on it. Save it with an appropriate name and you should find that you can send it. (I have not actually tried this!)

 

[TanisAgain]

Column count in a combo box starts from (0). Try changing your column count to (1)

 

[jool]

Update...

Thanks Tanis and dsigner

I have discovered I had specified the incorrect name for my dropdown menu button and the line should have read:

strToWhom = Me.Combo98.Column(2)

This however, is equally unsuccessful however, bringing the following error message instead:

Runtime error '2293'
Microsoft Office can't send this email message

Upon clicking Debug, the following line is highlighted:
DoCmd.SendObject , , , strToWhom, , , strSubject, strMsgBody, True

dsigner - I wasn't quite sure of your instructions (Sorry, I really am totally new to vba/access/ programming). How should I enclose my message in an object?

Tanis - yes, the email address was in the 3rd column (so 2 was okay)

Many thanks

 

[TanisAgain]

Have a look here. It may well provide the answer.

http://support.microsoft.com/kb/263084/en-us

 

[jool]

Hi TanisAgain,

Thanks for that. However, the 'fix' suggested seems a bit beyond me as I don't have rights to change the mail server settings, and in any event, am trying to set this up on an access form which could be used by several different people with perhaps different server settings.

Can anyone suggest an alternative method for doing this?

thanks

 

[jool]

Almost got it....

Just an update - someone from a different forum has suggested this code:

Dim strEmail As String, strSubject As String, strBody As String
strSubject = "Subject test"
strBody = "Test message"
strEmail = "mailto:" & Me.Email & "?subject=" & strSubject & "&body=" &
strBody
Application.FollowHyperlink Address:=strEmail

It seems to work only that it doesn't send the email automatically rather brings up a email box requiring me to click the send button. I actually want the mail to send automatically and bypass the box appearing...

Many thanks

Adam

 

[xaviermobius]

What version of outlook are you using - from version 2002 Microsoft added in security settings which require the user to allow an application outside of Outlook to send emails using the outlook service. This is requested via a alert box shown when the email object attempts to send. See the code below for another way to populate the email (template used).

Private Sub CommandButton1_Click()
Set myOlApp = CreateObject("Outlook.Application")
Set myItem = myOlApp.CreateItemFromTemplate("F:\absolutefilename.oft")

myItem.Subject = "mailsubjectline"
myItem.Attachments.Add ("F:\mailattachmentpath.xls")
myItem.Display

End Sub

add in the object references for recipient etc.

you may need to allow the reference library Microsoft Outlook 10.0 object library or whatever version.

 

[Fuga]

I got some help here a while ago:

http://www.access-programmers.co.uk/forums/showthread.php?t=92015&highlight=redemption

Fuga.

 

[dsigner]

Jool,
Sorry that it was not clear. I think that the Access intention was that you would create a report in the format of the email you wanted to send and then use sendobject to send it. I have shown a form with a single text instead.This should work if all the Internet hooks are set and can be found by Access on your machine. There is loads of instruction on how to create simple Access reports.

 

[jool]

Thanks

Hello all,

Many thanks for everybody's feedback.

Finally, all that I had to do was change the TRUE statement to FALSE (in the original code I posted) and the email sent without bringing up the error message. (I actually did not want the window box to appear, but being a non-programmer I did not know the what the TRUE function actually did).

I did not use the alternative code which I posted earlier.

Again, many thanks