Need clarification on VBA script used to send email via outlook

[dcdimon]

I have two questions regarding a script I made for sending out email in Access.

(1) I have a working VBA script that opens the Outlook application, if it's not already open, builds the email and then sends it, sort of. When Outlook isn't open the email gets stuck in the outbox until the application is actually running. Then when a send occurs the email is sent. If Outlook is open, then the email gets created and sent all at once.

I've done a search on the web and can't find an answer to whether Outlook needs to be open for the send to occur. Is the script only opening outlook to create the email and set it up to send the next time Outlook opens? Or is there a problem in my script that's causing this to happen.

(2) In my script I have both .Display and .Send. If I comment out, or remove the .Display, my script breaks and fails with an 'Application-defined or object-defined error" message and the .Send is highlighted. Am I missing something in the script that's causing me to need both of these items?

My code is below. Any insight would be appreciated.

Option Compare Database
Option Explicit
Sub CreateEmail()


'Generate email


    Dim OutApp As Object
    Dim OutMail As Object


' Opens outlook app and then closes it, if not open
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(olMailItem)


        With OutMail
        
            .To = "someemail@email.com"
            .CC = "moreemail@email.com"
            .BCC = ""
            
            'create subject line with file name, user and time submitted.
            .Subject = "Feedback on " & Left(CurrentProject.Name, Len(CurrentProject.Name) - 6) & _
            " from " & UserFullName & " - " & Date
            
            '.attachments.Add attmt
            .Body = "New feedback: " & vbCrLf & Forms!FeedbackF!Commenttxt & vbCrLf & "Left by " & UserFullName & vbCrLf _
            & vbCrLf & "This is an automated message, please don't reply directly to the user." & _
            vbCrLf & vbCrLf & "Palletizing QSB DBS - " & Now()
            
            
            'Need both .display and .send to autosend.
            
            .Display
            .Send


        
        End With




End Sub

DD

[Dal Jeanis]

This code

 Set OutApp = CreateObject("Outlook.Application")

opens a copy of outlook. So, no, it doesn't have to be running already.

The problem may be that you are hitting the end of the procedure and Outlook is closing before it gets a chance to send the message.

1) You can try executing DoEvents() after the "End With" to allow the operating system a chance to process the send command.
2) You can also put a little "For" loop at that spot to pause a second or two.
3) You can open a hidden form and use a form-boundl variable and a form timer for the outlook application and leave it running for some length of time before closing it.

[dcdimon]

DAL: I've tried putting in some sort of 'pause' function but the email still gets stuck in the outbox until Outlook is opened by the user. I think I'm just going to give up on having this be a truly automated email and request users start outlook before or after they use the database. It's the only way I can get this to work.

I still am looking for an answer to my second question. I can't get the script to work using just the .Send function. I have to include the .Display and .Send or I get an error message.

Can anyone shed light on why I have to do this? I've seen scripts out there that just use the .Send and the response that it worked. I cannot understand why I can't do that in my scripts.

Thanks for the assist.

[Dal Jeanis]

Oh, what's the specific error message if you just .send? In the scripts I've seen, .Display and .Send are mutually exclusive. I notice that one of the MS-posted routines for this had a .Save before .Send, so try adding that, just in case, and commenting out the .Display for the test.

It's also possible, if you are in a decent-sized organization, you might be running into something that is an organizational security rule or something. I'd expect certain other symptoms, though, since it would be pretty stupid security that let the email get created and only prevented it from sending until the person next started Outlook.

[dcdimon]

Here's what I discovered, quite by accident.

I found a sample database that would send out email. I loaded it and it worked as expected even without outlook open. I copied the script over to my email module and commented out what didn't apply and changed the email options (To, Subject..etc) to work with my email setup. Then I started commenting out some of the script. When I commented out the line that read .BodyFormat = olFormatRichText I got the 'object-defined' error. When I put it back into the script my email was created and sent out without any problems.

My guess is that without this, Outlook would open, but since it didn't know how to handle the formatting the email got stuck. Once Outlook opened, it applied the default format then off the email went. Is this possible?

My script is the same except for the addition of: .BodyFormat = olFormatRichText

Option Compare Database
Option Explicit
Public Sub CreateEmail()


'Generate email
    'Dim appOutLook As Outlook.Application
    'Dim MailOutLook As Outlook.MailItem
    
    Dim OutApp As Object
    Dim OutMail As Object


' Opens outlook app and then closes it, if not open
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(olMailItem)


       With OutMail
            .BodyFormat = olFormatRichText <==============new to script.
            '.To = "someemail@email.com"
            .CC = "someemail@email.com"
            .BCC = ""
            
            'create subject line with file name, user and time submitted.
            .Subject = "Feedback on " & Left(CurrentProject.Name, Len(CurrentProject.Name) - 6) & _
            " from " & UserFullName & " - " & Date
            
            '.attachments.Add attmt
            
            .HTMLBody = "New feedback: " & vbCrLf & Forms!FeedbackF!Commenttxt & vbCrLf & "Left by " & UserFullName & vbCrLf _
            & vbCrLf & "This is an automated message, please don't reply directly to the user." & _
            vbCrLf & vbCrLf & "Palletizing QSB DBS - " & Now()
            
            
            'Need both .display and .send to autosend.
            .Send
            '.Display
        End With


End Sub

DD

[June7]

Sounds like a reasonable conclusion. http://msdn.microsoft.com/en-us/libr...ffice.11).aspx

I have always included that line in code to send email. Now I know why it's needed.

[Dal Jeanis]

Cool find!

Is there HTML in your data that becomes the body of the email? Or is that required even when the body is plain text?

[June7]

Use .Body or .HTMLBody.

I have .HTMLBody with .BodyFormat set to olFormatRichText and it still works. But the text string has neither RTF nor HTML code tags.

Click on the triangle next to OlBodyFormat in that article to see 4 constants.

[dcdimon]

Cool find!

Is there HTML in your data that becomes the body of the email? Or is that required even when the body is plain text?

From what I found, the .Bodyformat needs to be included regardless of how you're getting the body text. I experimented with just script entered text (no html coding) and with pulling body text from a form control and without the .Bodyformat the email was created, but wasn't sent until Outlook opened.

I haven't found anything that says you have to use the .Bodyformat function when sending email, but that's how it seems to be functioning. Of course, when Outlook is open that's not an issue; the email goes right out then.

DD

[Dal Jeanis]

Okay, that's weird. In theory, the minute you create an Outlook application, outlook is "open".

Hmmm. I wonder if the .Bodyformat is somehow setting immediate focus to the Outlook app, whereas the other methods somehow aren't. Did you try using the .Save?