VBA to send email via outlook. Sendusingaccount

B

Boon

Hello,

I have been able to write a code that will send email via outlook. The next
improvement I would like to do is to select the account for outgoing email.
On my outlook I have 2 email accounts. (My personal account which is the
default. Another account is the shared account and it is the one I want my
VBA code using it.)

I found that there is a function Sendusingaccount and don't know how to use
it.

I would appreciate your help.

thanks so much,
Boon
 
M

Mark Andrews

Boon,

I wrote the following code (no guarantees, I'm testing it out with a client
in Singapore right now).

The top portion tries to match the outlook account (I think I found a code
example on microsoft's site somewhere).

HTH,
Mark Andrews
RPT Software
http://www.rptsoftware.com
http://www.donationmanagementsoftware.com


Public Sub CreateEmail(strFrom As String, strTo As String, strCC As String,
strBCC As String, strSubject As String, strTextOrHTML As String, strBodyText
As String, strBodyHTML As String)
On Error GoTo Err_CreateEmail
Dim OlApp As Outlook.Application
Dim olAccounts As Outlook.Accounts
Dim olAccount As Outlook.Account
Dim olAccountTemp As Outlook.Account
Dim olMail As MailItem
Dim FoundAccount As Boolean

Set OlApp = New Outlook.Application
FoundAccount = False

'loop through and find Outlook account based on from email address
Set olAccounts = OlApp.Application.Session.Accounts
For Each olAccountTemp In olAccounts
If (olAccountTemp.smtpAddress = strFrom) Then
Set olAccount = olAccountTemp
FoundAccount = True
Exit For
End If
Next


If (FoundAccount) Then
Set olMail = OlApp.CreateItem(olMailItem)
With olMail
.SendUsingAccount = olAccount
.To = strTo
.CC = strCC
.BCC = strBCC
.Subject = strSubject
If (strTextOrHTML = "HTML") Then
.BodyFormat = olFormatHTML
.Body = strBodyText
.HTMLBody = strBodyHTML
Else
.BodyFormat = olFormatPlain
.Body = strBodyText
End If
' .Attachments.Add filename
.Display
End With
Else
MsgBox "Could not find the appropriate Outlook account for email
address: " & strFrom & ". Contact RPT Software for assistance if needed.",
vbOKOnly, "Outlook error retrieving SMTP account"
End If

Exit_CreateEmail:
Set olMail = Nothing
Set olAccount = Nothing
Set OlApp = Nothing
Exit Sub

Err_CreateEmail:
MsgBox Err.Description
Resume Exit_CreateEmail
End Sub
 
L

Lee Taylor-Vaughan

Mark, You rock! works great!
thanks a ton buddy.. i've been trying to figure this out for ages!!!
Lee
 
L

Lee Taylor-Vaughan

one question.
How can i populate a combo box with the list of outlook SMTP accounts on a
form?
thanks
lee
 
M

Mark Andrews

Your second wish:
Here's some code in the form_load event of my email form:

'loop through available Outlook acoount and populate ComboFrom
Set OlApp = New Outlook.Application
For Each oAccount In OlApp.Session.Accounts
If oAccount.AccountType = olPop3 Then
Me.ComboFrom.AddItem oAccount.smtpAddress
End If
Next

If (Me.ComboFrom.ListCount < 1) Then
MsgBox "We cannot find any Outlook email accounts, so you cannot use
this email feature. Contact RPT Software for assistance if needed.",
vbOKOnly, "Error finding Outlook email account(s)"
DoCmd.Close
Else
Me.ComboFrom = Me.ComboFrom.ItemData(0)
End If
 
B

Boon

Hi,

Thanks Mark for your suggestion.

I am still stucked... It seems like the code you provided only select the
account type (exchange, live meeting,...)

In outlook2007, you can select the From Address. I can select the shared
email account to be sent from. For instance, my work email is
(e-mail address removed). The shared email address (the one I wish to send from) is
(e-mail address removed). And says my personal email is (e-mail address removed).

From my outlook, when I send email, the default From Address is
(e-mail address removed). I can change the From address to (e-mail address removed).
But I cannot change it to (e-mail address removed). This makes sense since the gmail
account is not on the company network..

Now, from Access, I would like to setup so that the email will be sent from
(e-mail address removed).

The reason I would like to do this is that I don't want to use my company
email to send emails to several people. I don't want them to reply to my
work email. I want them to reply to the shared email and thus I think I need
to send it from Shared email account.


thanks for your help.
Boon
 
B

Boon

Minutes after I replied I found out the solution!

It is pretty simple. Just set the .SentOnBehalfOfName ="
(e-mail address removed)"

thanks!!
 
M

Mark Andrews

Glad you figured it out.
Mark

Boon said:
Minutes after I replied I found out the solution!

It is pretty simple. Just set the .SentOnBehalfOfName ="
(e-mail address removed)"

thanks!!
 
L

Lee Taylor-Vaughan

Mark, You ROCK!
Thanks a ton.. works great!
Lee


Mark Andrews said:
Your second wish:
Here's some code in the form_load event of my email form:

'loop through available Outlook acoount and populate ComboFrom
Set OlApp = New Outlook.Application
For Each oAccount In OlApp.Session.Accounts
If oAccount.AccountType = olPop3 Then
Me.ComboFrom.AddItem oAccount.smtpAddress
End If
Next

If (Me.ComboFrom.ListCount < 1) Then
MsgBox "We cannot find any Outlook email accounts, so you cannot
use this email feature. Contact RPT Software for assistance if needed.",
vbOKOnly, "Error finding Outlook email account(s)"
DoCmd.Close
Else
Me.ComboFrom = Me.ComboFrom.ItemData(0)
End If

--
Mark Andrews
RPT Software
http://www.rptsoftware.com
http://www.donationmanagementsoftware.com
 
Joined
May 18, 2011
Messages
1
Reaction score
0
Dear Sir,

yahoo has allotted me 1000 email id and I can send 250 emails per day per ID.


I wish to do mass mailing but and every time i need to change out look email properties.

i will create one table in excel with following headings.

Display Name
Email Address
Usename
Password

Is there any option in excel vba where my codes will read from this table and change the outlook properties and send the mail.
One its reach to 250 my cursor will move to next row.


Regards
Shankar More
(e-mail address removed)
India
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top