Automating Outlook from Access

[RMittelman]

Hi, trying to send outlook email from Access 2007.
I am using the following code. for brevity, I replaced some of the code with comments.

    Dim oOutlook As Outlook.Application
    Dim oMsg As Outlook.MailItem
    Dim oRecipient As Outlook.Recipient
    Dim oAttachment As Outlook.Attachment
    
    Set oOutlook = New Outlook.Application
    Set oMsg = oOutlook.CreateItem(olMailItem)
    With oMsg
        .Display
        'Set Recipients
        'Set Subject
        'Set message body
        'Set importance
        'Set attachment(s)
    End With
    Set oRecipient = Nothing
    Set oAttachment = Nothing
    Set oMsg = Nothing
    Set oOutlook = Nothing

This works perfectly. It shows a new outlook message, and fills in the data I wanted. I deliberately did not automate the sending itself, because I want to proof it first.



All I need to do is click the "Send" button.
At least that was the plan. It never gets sent.
When I actually open up Outlook, I see the message for a moment in the outbox, and THEN it gets sent without me needing to do anything.

So it seems like Outlook itself must be visible for this to work when I click the send button.

(By the way, this same thing happens from my Quicken when I try to send a customer invoice. Quicken does everything, but the message stays in the outbox until I open Outlook.)

So, the question is: Is this a behavior I can control with an Outlook setting? If not, how do I make the ENTIRE Outlook application visible from Access VBA, rather than just the new message?

Thanks...

[ajetrumpet]

I think this IS a setting, and there is one in the options called "send immediately when connected".. I believe it is at:

tools > options > mail setup > send immediately when connected (checkbox).

I know this sends messages immediately when you hit the send button, but your smtp server, or whatever you're using is obviously never going to be faster than your vba code that's running.

It's been a while since I've done this too, so I'm not sure if that is actually stopping the sending process for you. But that option has to be checked, otherwise it'll never even have a chance to work. The only time (I think) Outlook automates send/receive while it's open, other than that above option, is the periodic send/receive option that's in the tools > send/receive menu.

[RMittelman]

Thanks Adam.

You are right that it is an Outlook setting. Unfortunately, that setting is already, well, set.

Since the problem occurs when I automate from Access AND when my Quicken sends an invoice via Outlook, I may need to switch to the Outlook forum and ask the question there.

If I run the code which brings up a pre-filled email message, and click the "Send" button, it looks like it's sent, but never goes unless I open up Outlook, then the message is there in the outbox momentarily, then gets sent by itself.

If I open Outlook either before running the code OR after running the code, with the email message already showing, then click "Send", it works fine.

I wanted to verify how this works, however, and possibly use Access to prove there is a problem with Outlook.

So, before moving to the Outlook forum, can you or anybody tell me how to programatically make the Outlook window visible from VBA code, rather than only the email message?

Thanks...

[ajetrumpet]

it should be as simple as:

oOutlook.visible = true

my guess is that the code is closing the instance of outlook before the program has a chance to send the message through the server.

I would certainly try that forum, because this kind of question is decades old and millions have asked it already, so someone has obviously gotten it to work the correct way. Either that, or noone has ever noticed that the messages simply sit in the outbox. =)

the other thing you might try doing is using an external server to send mail, without using outlook at all. there are websites to download the code from. I could even upload a sample for you, as I've made it work myself.

another option would be the CDO library (dll).

[RMittelman]

Thanks again Adam.

That would have been so easy, but the Outlook object does not have a .Visible property. If I force one in anyway, the code compiles ok, but I get a runtime error 438: Object does not support this property or method.

The code does dispose the objects after building the message:

    Set oRecipient = Nothing
    Set oAttachment = Nothing
    Set oMsg = Nothing
    Set oOutlook = Nothing

But I think it's only killing references to the "real" objects, since I can still see the message, click on it, edit it, then click "Send". The code itself is not actually trying to send the message.

Your other 2 suggestions, which are good ones, won't help here because as mentioned earlier, my Quicken function to create and send an email invoice has the same issue. That one doesn't even show the message, just builds and sends. Like the one created in Access, the message dutifully sits there in the Outbox waiting for me to actually open Outlook.

Anyway, thanks again for being so responsive. I will definitely post this in Outlook forum (without all the dirty code details) and see what they say.

[susylingle29]

Can you modify the code to access a calendar that is not the default? In other words, a shared calendar (Outlook resource)?

[RMittelman]

Suzy,
I'm not sure what you are asking here. Nothing in this thread has anything to do with accessing Outlook calendars, only using it to send an email.

Can you clarify why you are asking about calendars?

[susylingle29]

Instead of sending an appointment to my default calendar, I want to send the appointment to a shared calendar in an Outlook public folder.

[RMittelman]

Sorry, I don't know how to create an appointment in code, only how to send an email message.

[RMittelman]

I have an Access form whose purpose is to import data changes into a table.

Data changes are stored in a table called MemberEdits, which basically has the same columns as my Members table.
MemberEdits is populated by importing data from an Excel worksheet containing those changes.

The main form has 2 subforms called MemberEditsSubform and MembersSubform. Each are continuous forms (not datasheet) and look almost identical to each other.
Both subforms contain too many fields to display entirely, so they both have horizontal scrollbars.

My intent is that when the user scrolls one of these by dragging the horizontal scrollbar or clicking in the empty area or clicking the right- or left-arrow on the scrollbar, the scrollbar in the other subform will automatically scroll horizontally to match that position.

I'm familiar with using Windows API's, and I *think* I can make use of GetScrollInfo and SetScrollInfo API's.
I've seen an example of GetScrollInfo which returns a scroll position, but it was for a ListBox, which has a Scroll event.

I don't believe an Access form has a Scroll event, so I don't know how to call the code needed.
Does anybody have an idea how to trigger an event when you scroll the form horizontally? Would SendMessage help at all?

Thanks...

[Micron]

WOW. This is a first for me. The OP comes back almost 11 years later and hijacks their own thread with a totally unrelated question!
As far as I can tell, in Access there is no listbox scroll event, nor is there a scroll method. Maybe you're thinking of an API. FWIW, I'd say you're better off creating a new thread because the title of this one has nothing to do with what you're now asking.

[Micron]

WOW. This is a first for me. The OP comes back almost 11 years later and hijacks their own thread with a totally unrelated question!
As far as I can tell, in Access there is no listbox scroll event, nor is there a scroll method. Maybe you're thinking of an API. FWIW, I'd say you're better off creating a new thread because the title of this one has nothing to do with what you're now asking.

EDIT - OK, now I just saw this new thread https://www.accessforums.net/showthread.php?t=84520
when I closed this one.