Setting email Account from Access

[Massimo]

Hello. I need to set the email account from Access. I have a list of account
in Access and I need to use one of them as an account for an Outlook mail. I
tried with the following code:

Dim frm As Form
Dim itmnewmail As MailItem
Set itmnewmail = outlook.CreateItem(olMailItem)
Set frm = Forms(Nz(strfrm, ""))

itmnewmail.SendUsingAccount = Nz(frm.cmbda,"")

frm is my form in Access, cmbda is a combo for chosing an account from a list.
This code works to set the

itmnewmail.to

field, but not for the account sender.

Can anyone help?
Thanks.

[Sue Mosher [MVP-Outlook]]

This is Outlook 2007? (Please always include your Outlook version in your posts.)

SendUsingAccount takes an Account object, not a string, so something like this:

itmnewmail.SendUsingAccount = Outlook.Session.Accounts(Nz(frm.cmbda,""))

--
Sue Mosher, Outlook MVP
Author of Configuring Microsoft Outlook 2003
http://www.turtleflock.com/olconfig/index.htm
and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers
http://www.outlookcode.com/jumpstart.aspx

"Massimo" <Mas...@discussions.microsoft.com> wrote in message news:0708EF1D-AF5C-481A...@microsoft.com...

[JSOBERWE]

I am having the same trouble with this. What I am trying to do is use the
.SendUsingAccount property to send an e-mail from an Excel module using a
non-default Outlook account. Environment is:
• Vista Ultimate 64-bit
• Office 2010 Beta 64-bit
• E-mail setup is Exchange, NOT POP3 or SMTP
• E-mail is setup so my profile has full access to the mailbox
“AutoReports”. For clarity, it's added under Account Setting>Change>More
Settings>Advanced
• The code is going in an Excel VBA module.

There is a lot of code in the spreadsheet doing a bunch of different things,
and this code is just test code to get this one feature to work properly.
I’ll integrate it once I get it successfully tested. I suspect something is
just plain wrong in the highlighted area, but I can’t figure out what.

Here's the code:

Sub test()
Set rng = Sheets("SharedRepIDs").Range("A1:L7")

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

Dim OutAcct As Outlook.Account
Set OutNS = OutApp.GetNamespace("MAPI")
OutNS.Logon
Set OutAcct = OutNS.Accounts.Item("sending...@domain.com")

'On Error Resume Next
With OutMail
.SendUsingAccount = OutAcct
.To = "toac...@domain.com"

.Subject = "Test"
.HTMLBody = "Test"
.Send
End With
On Error GoTo 0

With Application
.EnableEvents = True
.ScreenUpdating = True
End With

Set OutMail = Nothing
Set OutApp = Nothing
End Sub

Does anyone know why I may be doing wrong? I can't get the SendUsingAccount
to work! Ugh!

[Michael Bauer [MVP - Outlook]]

The Set statement is missing:
Set .SendUsingAccount = OutAcct

--
Best regards
Michael Bauer - MVP Outlook
Manage and share your categories:
<http://www.vboffice.net/product.html?pub=6&lang=en>

[JSOBERWE]

Where should it go? In my original posted code, I had ".sendusingaccount =
OutAcct" after "With Outmail". Did you see that?

When I put:

Set SendUsingAccount = OutAcct

before the "with outmail" statement, I get an error "Expected function or
variable" on that line. I commented out the ".sendusingaccount" line.

When I instead replace the ".sendusingaccount" line in my originally posted
code with the line above, I get also get that error. The difference between
the two is whether the above statement is before or after "With OutMail".

So, I guess several questions:

1. Did you see that I had the .sendusingaccount line after With OutMail?
2. If yes, where should I insert the line you gave me?
3. If no, does that change your advice?

Thanks so much for your help. This one is just driving me nuts!

[Michael Bauer [MVP - Outlook]]

Simply replace your line of code with my mine.

--
Best regards
Michael Bauer - MVP Outlook
Manage and share your categories:
<http://www.vboffice.net/product.html?pub=6&lang=en>

[JSOBERWE]

Right - I tried that before my 2nd post. It didn't work. To be sure, I tried
it again after your post. That line then generates the following error:
"Runtime error '5'. Invalid call procedure or argument."

Thanks so much for helping me!

Here's the modified code for clarity:

Sub test()

Set rng = Sheets("SharedRepIDs").Range("A1:L7")

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

Dim OutAcct As Outlook.Account
Set OutNS = OutApp.GetNamespace("MAPI")
OutNS.Logon
Set OutAcct = OutNS.Accounts.Item("sending...@domain.com")

'On Error Resume Next
With OutMail

Set .SendUsingAccount = OutAcct

[Graham Mayor]

You will get such an error if you don't have the correct Outlook account
name?

Set OutAcct = OutNS.Accounts.Item("sending...@domain.com")

Use the name displayed in your Outlook Tools > Accounts Settings list which
is probably not the e-mail address.

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

"JSOBERWE" <JSOB...@discussions.microsoft.com> wrote in message
news:0249D267-8EA2-4A39...@microsoft.com...

[Ken Slovak - [MVP - Outlook]]

Correct.

However, the item.SendUsingAccount property is what's called an object
property. Starting in Outlook 2003 to some extent, and continuing more
generally in Outlook 2007, the developers started following what really is
the correct pattern.

Instantiating an object (Set OutAcct = blah) is correct. Setting an object
property doesn't instantiate anything, it just sets that property. So that
would be .SendUsingAccount = OutAcct, without the Set.

It's confusing because object properties added earlier to the object model
(in 97, 98, 2000 and 2002) do not follow the correct pattern and do require
Set to set an object property.

--
Ken Slovak
[MVP - Outlook]
http://www.slovaktech.com
Author: Professional Programming Outlook 2007.
Reminder Manager, Extended Reminders, Attachment Options.
http://www.slovaktech.com/products.htm

"Graham Mayor" <gma...@REMOVETHISmvps.org> wrote in message
news:eq7UB%23JsKH...@TK2MSFTNGP04.phx.gbl...

[JSOBERWE]

Is there any chance you could adjust the code where it's wrong? I've tried
this every way I can think of. I tried "Set sendusingacct = " and
".sendusingaccount=". I've tried using the full account e-mail address, and
I've tried just how it appears. It appears in the dialog box as "Mailbox -
AutoReports". So, I've tried it just like that (with the "Mailbox") and just
as "AutoReports".

I have literally tried every combination and permutation imaginable, and I
can't get it to work. Here is the code that I believe reflects what Ken is
saying, and it doesn't work. While it doesn't generate any errors, it just
sends it from my own account and not the non-default account "Autoreports".

Would you mind just editing this as it needs to be edited? I am apparently
not able to figure it out myself.

Sub test()

Set rng = Sheets("SharedRepIDs").Range("A1:L7")

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

Dim OutAcct As Outlook.Account
Set OutNS = OutApp.GetNamespace("MAPI")
OutNS.Logon

Set OutAcct = OutNS.Accounts.Item("AutoReports")

'On Error Resume Next
With OutMail

.SendUsingAccount = OutAcct
.To = "joe.ob...@oberweis.com"

[JSOBERWE]

Two updates:

1. I tried this on Office 2007 with the same result.

2. I think there may be confusion on the e-mail environment. In my original
original post I tried to explain this as follows "E-mail is setup so my

profile has full access to the mailbox “AutoReports”. For clarity, it's added
under Account Setting>Change>More Settings>Advanced "

In other words, we are in an Exchange environment. Only a single account is
listed in the first Account Settings screen. To see the AutoReports account,
you click "Change" then go to More Settings, then Advanced. There is a box
where additional Exchange mailboxes to which I have permissions can be set.

It's NOT POP3 or SMTP.

[JSOBERWE]

Also, I just set the account up as a POP3 account so does appear on the list
of accounts screen to no avail. UGH!

[Ken Slovak - [MVP - Outlook]]

I think you are confusing email accounts and delegate mailboxes. The dialog
you reference is for delegate mailboxes, it has nothing to do with your
email accounts.

In addition, SendUsingAccount is only available for Outlook 2007 and later.
If you are trying to use it in an earlier version of Outlook it's not there
and obviously will never work.

--
Ken Slovak
[MVP - Outlook]
http://www.slovaktech.com
Author: Professional Programming Outlook 2007.
Reminder Manager, Extended Reminders, Attachment Options.
http://www.slovaktech.com/products.htm

"JSOBERWE" <JSOB...@discussions.microsoft.com> wrote in message

news:F1B29402-116C-414B...@microsoft.com...

[Michael Bauer [MVP - Outlook]]

Have you checked whether the variable is Nothing? I don't remember the error
description, but Set SendUsingAccount=Nothing throws an error.

--
Best regards
Michael Bauer - MVP Outlook
Manage and share your categories:
<http://www.vboffice.net/product.html?pub=6&lang=en>

[Michael Bauer [MVP - Outlook]]

I've read about that too. But calling it with the Set statement still works.

--
Best regards
Michael Bauer - MVP Outlook
Manage and share your categories:
<http://www.vboffice.net/product.html?pub=6&lang=en>

[JSOBERWE]

Any chance you can look at the last code and just edit it directly? I am
aware it only works in 2007 and later. The original version I've been working
is is 2010 Beta. I thought I'd try 2007 directly to be sure it wasn't a beta
problem.

I've now setup the account as a POP3 account where you suggested, and I
still can't get it to work. Would you mind telling me where this code is
wrong?

[JSOBERWE]

I finally figured it out! It has to be setup as a POP3 account and use this
code:

Sub SendUsingAccount()

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon

Dim oAccount As Outlook.Account
Set oAccount = OutApp.Session.Accounts.Item("AutoReports")
Dim oMail As Outlook.MailItem
Set oMail = OutApp.CreateItem(olMailItem)
oMail.Subject = "Subject"
oMail.HTMLBody = "Body"
oMail.To = ("us...@domain.com")

oMail.SendUsingAccount = oAccount
oMail.Send

End Sub

[Dmitry Streblechenko]

On the low level, there are two ways to set a property - INVOKE_PROPERTYPUT
and INVOKE_PROPERTYPUTREF.
Wherever OOM used to have INVOKE_PROPERTYPUTREF only, a second property
setter wad added to to support INVOKE_PROPERTYPUT. You can use either one in
the latest versions of Outlook.

--
Dmitry Streblechenko (MVP)
http://www.dimastr.com/
OutlookSpy - Outlook, CDO
and MAPI Developer Tool
-
"Michael Bauer [MVP - Outlook]" <m...@mvps.org> wrote in message
news:1ns6jw23jh7s3$.1vtt5flahvnrf.dlg@40tude.net...

[Michael Bauer [MVP - Outlook]]

Thanks for the details, Dmitry.

--
Best regards
Michael Bauer - MVP Outlook
Manage and share your categories:
<http://www.vboffice.net/product.html?pub=6&lang=en>