Automatically send email from form. 3

[tmel1690]

Hi,
I am trying to automatically send a confirmation # email after placing an order. I have the orders placed on a form with a command button that will start the event procedure. However, when I run this code, I get a run-time error message: Outlook does not recognize one or more names. Then when I go to debug it - the arrow points to the .send part of my code. I am using Acess 2003 and MS Outlook. Does anyone know what I'm doing wrong?

Private Sub Command96_Click()
Dim EmailAddress As String
Dim Autonumber As String
Dim Text97 As String

'Create variables for Outlook
Dim objOutlook As Outlook.Application
Dim objemail As Outlook.MailItem
'Gather information from form.
EmailAddress = Me!EmailAddress
Autonumber = Me!Autonumber
Text97 = Me!Text97
'Creates an instance of Outlook
Set objOutlook = CreateObject("Outlook.application")
Set objemail = objOutlook.CreateItem(olMailItem)
'Creates and send email
With objemail
.To = EmailAddress
.Subject = Autonumber
.Body = Text97
.Send
End With
'Closes outlook
Set objemail = Nothing
objOutlook.Quit

End Sub

 

[Blorf]

Hi.

Change the .send to a .display and see what gets put into the to box. Maybe the emailaddress variable is corrupt or something.

ChaZ

 

[PHV]

First, at least for debug, try .Display instead of .Send
Next, seems the EmailAddress don't pass the outlook's validation tool.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[tmel1690]

Blorf and PHV - Thanks for your help! I got it to work!

But now I have two other questions/problems.
1. Is there a way I can combine the VB email code to a macro that I had on that form before - it closed and saved the form while looking for required fields.
2. How do I write a code so that when the email address is null it will not run my code. Right now, if it is null, an error message shows up. (I'm not VB literate- I used my earlier code from other messages on this site and applied it to my own controls.)

 

[Blorf]

Hi.

First you can check to see if your to box is empty.

if isnull (Me!EmailAddress) then
msgbox "You need an address"
else
Do your e-mail code
endif

Regarding attaching to a Macro, I don't know exactly what you mean.

ChaZ

 

[tmel1690]

I knew it was unclear when I posted it. Sorry. Let me rephrase - in my form I already have a command button that uses a macro to check certain fields that I made required before the user can save and close the form. But now I want to add this email code, too (but it's in VB). Is there some way to include this code into my macro by using a macro action like a run command or run code?

 

[Blorf]

Sort of.

If you create a module, and create a function in the module, put the code in that, then you can call the code in the macro with the runcode command in the Macro.

ChaZ

 

[tmel1690]

Hi Blorf,
I am really bad at VB. I think I'm going crazy. I tried putting the isnull statement after .to in my code. But I kept getting messages about a null statement. I know it's because I leave my EmailAddress field blank. But I was wondering how would I write a statement to say if the "EmailAddress" field is null then to cancel the procedure? (Not all customers will have an email address to send a confirmation # to.) And also, in your else statement--I don't know what to write when you tell me to "do your e-mail code" to continue running the code. I'm sorry, I told you I am very bad at VB.

if isnull (Me!EmailAddress) then
msgbox "You need an address"
else
Do your e-mail code
endif

Thanks a million for your help!

 

[Blorf]

Hi. Below is an example of what I mean. Hope it works out.

ChaZ



Private Sub Command96_Click()
Dim EmailAddress As String
Dim Autonumber As String
Dim Text97 As String

if not isnull (me!emailaddress) then

'Create variables for Outlook
Dim objOutlook As Outlook.Application
Dim objemail As Outlook.MailItem
'Gather information from form.
EmailAddress = Me!EmailAddress
Autonumber = Me!Autonumber
Text97 = Me!Text97
'Creates an instance of Outlook
Set objOutlook = CreateObject("Outlook.application")
Set objemail = objOutlook.CreateItem(olMailItem)
'Creates and send email
With objemail
.To = EmailAddress
.Subject = Autonumber
.Body = Text97
.Send
End With
'Closes outlook
Set objemail = Nothing
objOutlook.Quit

else

msgbox "no email address available"

end if

End Sub

 

[tmel1690]

Hi -
I wrote in that code and ran it. Now it tells me that I have a compile error: End with without with. Any thoughts on this again? Again thanks for all your help you've been giving me.

 

[Blorf]

Hi. Post your code as you have it, and maybe I will see it. The with and end with are both within an if, so I don't see the problem right away.

ChaZ

 

[tmel1690]

Here's my code. I changed some of the field names to what I intend to have in my database.

Private Sub SCEmail_Click()

Dim Email As String
Dim Autonumber As String
Dim EmailMsg1 As String
Dim EmailMsg2 As String
Dim EmailMsg3 As String
Dim EmailMsg4 As String
If Not IsNull(Me!Email) Then

'Create variables for Outlook
Dim objOutlook As Outlook.Application
Dim objemail As Outlook.MailItem
'Gather information from form.
Email = Me!Email
Autonumber = Me!Autonumber
EmailMsg1 = Me!EmailMsg1
EmailMsg2 = Me!EmailMsg2
EmailMsg3 = Me!EmailMsg3
EmailMsg4 = Me!EmailMsg4
'Creates an instance of Outlook
Set objOutlook = CreateObject("Outlook.application")
Set objemail = objOutlook.CreateItem(olMailItem)
'Creates and send email
With objemail
.To = Email
If IsNull(Email) Then
Exit Sub
Else: Call SCEmail_Click
.Subject = EmailMsg1 & " " & Autonumber
.Body = EmailMsg2 & "" & EmailMsg3 & "" & EmailMsg4
.Send
End With
'**closes outlook
Set objemail = Nothing
objOutlook.Quit
Else
MsgBox "No E-Mail address available"
End If


End Sub

Thanks again!

 

[RoyVidar]

Try replacing

[tt] If IsNull(Email) Then
Exit Sub
Else: Call SCEmail_Click[/tt]

with

[tt] If IsNull(Email) Then
Exit Sub
Else
Call SCEmail_Click
end if[/tt]

Roy-Vidar

 

[tmel1690]

Thank you, thank you Blorf and RoyVidar! You are the best!! It finally works! Yea! One final question: When the command button is clicked and the email is ready to be sent, a MS pop-up comes up and says, "A Program is trying to automatically send email on your befalf. Do you want to allow this? If this is infected, it may be a virus and you should choose "No."--- Is there a way to write a code so that this notice doesn't show up at all? If not, let's say the user presses "No" by accident, an error message pops up and says "Application defined or object-defined error" and takes me to ".send" again in my vb code. Is there a code I can write to counteract the error message? Thanks again for all your help!

 

[Blorf]

Hi.

I can't help with the message you receive. I understand there is software available but I don't know about it.

Regarding the error messages though, perhaps using an on error statement would work.

ChaZ

 

[PHV]

Do a google search for outlook object model guard
Seems that redemption is a good tool.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244