Solved

Send email with VBA - how to select/load the account?

Posted on 2008-06-26
29
1,273 Views
Last Modified: 2013-11-27
Hi I have a simple VBA code that allows me to send emails using a MS Access DB.
Could you post a few lines that make possible to previously load in Oulook the email account that I would like to use for these transmission? Thanx.
0
Comment
Question by:Sar1973
  • 13
  • 8
  • 4
29 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
Sar1973,

I don't know if this is possible, because you might have to enter a password.

Pleaes explain, in dertail, what you mean by:
<load in Oulook the email account that I would like to use for these transmission>

JeffCoachman
0
 
LVL 9

Author Comment

by:Sar1973
Comment Utility
No passwords. I have say 2 account in my Outlook 2000: I want to choose from which one the email should be sent.
I also want to know if it is possible to inidicate the name of the recipient with its email address (say FakeAddress@unreal.com "DR.SAR"), so that in the "To" field the recipient may see its name and not only its email address. Thanx.



{08-Sep-08:  email address edited for spam prevention -- mbizup, Access Zone Advisor/Page Editor}
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
Sar1973,

You might want to add the Exchange Zone to this question.
(Click the "Request Assistance" button in your original post)

If you could specify the account (Which again, I am not sure of), you would have to make sure that the Recipient HAD a "real" name in addition to an email address.
(Outlook will uses the real name by default)

JeffCoachman
0
 
LVL 9

Author Comment

by:Sar1973
Comment Utility
I am still waiting for an answer; definitely, I am an Outlook 2000 user that wants to select a sender account alternative to the default when creating with VBA an email message.
The ".from=email@provider.it" VBA command does not work; I have to select the account manually in the email preview, clicking on the "From:" button or typing it in the beside space.
Thanx.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
Vee,
Can you add the Exchange Zone?
Jeff
0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
Sar1973,

This function, using CDO allows me to set the "FROM" address, assuming that it is already set up in Outlook.

One of the nice things about using CDO is that you don't get the warning messages that you get sending emails using outlook objects (although you might not get these warnings in OL 2000 anyhow), and you have the same options.

Call the function like this if you have a specific From address that you want to use:
cdosendemail "MyFromAddress@MyEmail.com","YourToAddress@YourEmail.com", "Interesting subject", "Totally Brilliant Message"

The function also lets you use a default From address by using NULL or empty string ("") in the varFrom argument:
cdosendemail "","YourToAddress@YourEmail.com", "Interesting subject", "Totally Brilliant Message"
cdosendemail Null, YourToAddress@YourEmail.com", "Interesting subject", "Totally Brilliant Message"








Function CDOSendEmail(varFrom As Variant, strTo As String, strSubj As String, strMess As String)

    Dim strDefaultFrom  As String

    Dim objMess As Object

    Set objMess = CreateObject("CDO.Message")

      

    ' This is your default FROM address, and to be used if varFrom is Null or Empty

    strDefaultFrom = "DefaultAddress@MyEmail.com"

    

    ' Subject

    objMess.Subject = strSubj

    

    ' Message

    objMess.textbody = strMess

    

    ' Determine if varFrom is Null/Empty and use default address,

    ' otherwise use the From address passed through the function call

    objMess.From = IIf(Nz(varFrom, "") = "", strDefaultFrom, varFrom)

    

    ' To

    objMess.To = strTo

    

    ' This is a simple example... you can do a lot more

    

    ' Send it

    objMess.Send

    

    ' Clear the object variable

    Set objMess = Nothing

    

End Function

 

Open in new window

0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
LOL,

Yes it seems that the "mbizup Zone" has already been added to this Q!
;-)

Jeff
0
 
LVL 9

Author Comment

by:Sar1973
Comment Utility
Sorry, I get an error on "objMess.Send": it says "Property not supported", as when I try to launch the ".From=..." command with the other code. The real problem is that if I create an OL2000 model, I set by default the sender address and I try to save it in order to use that address on each message I create, it does not save the sender address in the .oft file. Any idea...?
0
 
LVL 9

Author Comment

by:Sar1973
Comment Utility
Maybe it is possible with HTML, doing something like:
olkmsg.HTMLbody = olkmsgHTMLbody & "<B>From:</B>address@email.it<BR><B>"
0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
Sar1973,

>I get an error on "objMess.Send": it says "Property not supported", as when I try to launch the ".From...
>Any idea...?

Nothing sound :-(

The code I posted works on this end, so it sounds like a configuration difference.

Some thoughts:

- Did the code fail for you exactly as I posted it, or was it a problem with integrating it into your own code?
- Are there any security settings that might be preventing this from working (for example, I can't use CDO to programmatically send emails from my work computer)
- This function works for me using Office 2007 and 2003, but I don't have 2000 (it's entirely possible, as Jeff suggested, that this will not work with OL 2000).
- Can you test these functions on any other computers to isolate configuration differences as problems?


Also -- can you post your original email function here (full code)?



0
 
LVL 9

Author Comment

by:Sar1973
Comment Utility
-I posted it exactly as you did
-No
-I guess that's the problem: I asked for OL2000
-Same trouble

Public Function PrepareEmail(...)
 

    Dim olkApp As Object, olkMsg As Object
 

    Set olkApp = CreateObject("Outlook.application")

    Set olkMsg = olkApp.CreateItemFromTemplate(myModel)
 

    With olkMsg

            .ReadReceiptRequested = True

            .To = oIndirizzo

            .HTMLBody = oHTML

            .Attachments.Add myAttach

            .Save

    End With
 

    Set olkApp = Nothing

    Set olkMsg = Nothing
 

End Function

Open in new window

0
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
Comment Utility
Sar1973,

A few more ideas:

> "Property not supported",
This is like a syntax error for the message object- it is a property or method that is not recognized, and you would get the same results using a line like:

objMessage.Moo

I don't see anything wrong with your original code. However you mentioned getting this error when you use the .From property. This property is not associated with outlook message objects at all in 2003 or 2007 (it is simply not an available option) -- and I am guessing based on the error message that it is not available in OL 2000 either. The closest thing I found that was not read-only was the .SentOnBehalfOfName property. Give that a try - and see if it is close enough.

The same thing seems to be happening using my code with the .Send command under your software configuration (although it's surprising to me that earlier commands did not produce the same error on your side).

Both of us are using late binding in our code which is advantageous in that you do not need to change reference settings when using a databse with different versions of the CDO or Outlook libraries on different computers.

However, you could try declaring test message objects using early binding to see what your available property and method options are using OL 2000 and whatever version of the CDO library you have. With early binding, you can use VBA's intellisense to your advantage - to see a drop down list of options associated with these objects. See this reference for more information/examples if needed:

http://word.mvps.org/fAQs/InterDev/EarlyvsLateBinding.htm
-----------------------------------------------------------------------------------------------------------


Try this...

First set your references:
- From the VBA editor, Tools -> References
- Check the box for Microsoft Outlook {ver. no} Object Library
- Check the box for Microsoft CDO For Windows 2000 {or other version} Object Library
- Click the OK button to save and exit

Then try the following two code scenarios which use Early Binding...

Using an Outlook object (your code), try the following "test" statements:
Dim olkTestMsg As Outlook.MailItem
Set olkTestMsg = Outlook.CreateItem(olMailItem)
olktestmsg.{after typing this first part in, review the available options in the drop down list to see if anyhing resembles what you need.}


Similarly for the CDO object (my code) try these test statements:

Dim objTestMess As CDO.Message
Set objTestMess = New CDO.Message
objTestMess.{look for options similar to "send" - possibly SendEmail? }





( If none of this works/helps, I am completely out of ideas...)





0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 9

Author Comment

by:Sar1973
Comment Utility
I will: in the meanwhile can you tell me if the HTML method can be used instead?
0
 
LVL 9

Author Comment

by:Sar1973
Comment Utility
I have tried to select CDO library and create the message: no way. I am really thinking of developing 1 or 2 lines of code useful to add HTML body which configures the sender.
After the first attempts, I have managed only to add "From: adress@email.it" in the body of the email, not in the head. Any idea...?
0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
Sar1973,

I don't know how to make the HTML Body idea work.

> I have tried to select CDO library and create the message: no way.

Can you be more specific (error messages, missing library, etc)?

I will try to send other Experts this way.

0
 
LVL 9

Author Comment

by:Sar1973
Comment Utility
0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
That error is in a different block of code.

> I have tried to select CDO library and create the message: no way.
My question concerned what the specific problem was using CDO in your last test in *this* thread.

bluedevilfan's approach very well might work with OL 2000.

EE allows a maximum of 500 points per question. Since you are receiving help on a duplicate question, you should request that this one be deleted (the Delete button in your original post).

Also consider VM's comments in your recent Community Support thread.
http://www.experts-exchange.com/Community_Support/General/Q_23711599.html#22419554
Since you have unlimited points, raising the points in your duplicate question can't hurt. As both of these threads are showing, getting this working using Outlook 2000 is not a trivial issue.


0
 
LVL 9

Author Comment

by:Sar1973
Comment Utility
Just explain how to raise the points assigned to this question, or do it yourself.
0
 
LVL 9

Author Comment

by:Sar1973
Comment Utility
Would the .Account property be useful to the scope?
0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
Sar1973,

The conclusion I've come to here is the same as what BlueDevilFan has stated in your other question.  This type of code works fine with Outlook 2003 and 2007, but there is no way to do this directly in Outlook 2000.

Please see the following thread, including the comment and link provided by Sue Mosher (Outlook MVP) at the very end:
http://www.devnewsgroups.net/group/microsoft.public.office.developer.outlook.vba/topic51978.aspx

The link that she has provided shows you how to accomplish this indirectly, by programmatically "clicking" the necessary command buttons and selecting the account (using Outlook's Inspector).

The code involved is very similar to what BlueDevilFan has already posted in your duplicate question.

The point of my previous comment:
    -  EE does not allow duplicate questions.
    - BlueDevilFan has already provided you with the basic method needed to solve this problem.
    - You should delete this question and continue to work with the other one.
0
 
LVL 9

Author Comment

by:Sar1973
Comment Utility
I finally found it: .SentOnBehalfOfName = "address@email.it"
0
 
LVL 9

Author Comment

by:Sar1973
Comment Utility
No solution, I found it elsewhere.
0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
Sar1973,

I did suggest that you try "SentOnBehalfOfName" as a workaround in this earlier post:
http://www.experts-exchange.com/Software/Office_Productivity/Groupware/Outlook/Q_23518737.html#22445910

Have you tried sending a message like this to yourself?

The CDO method I posted and also the .From property in more recent versions of Outlook will only show the recipient the .From address that you specified.

The reason I called the "sentOnBehalfOfName" property a workaround is that the recipient sees both your actual address and the From address that you specify in code. Like this:

You [You@RealEmail.com]; on behalf of; YourSentonBehalfofName@OtherEmail.com
From your follow-up comments, I had assumed that was not an acceptable solution.


> No solution, I found it elsewhere.

At any rate, if that is the solution you are using, I did post it in this thread.





0
 
LVL 9

Author Comment

by:Sar1973
Comment Utility
Maybe, between the lines.
0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
From http:#a22445910 -

< The closest thing I found that was not read-only was the .SentOnBehalfOfName property. Give that a try - and see if it is close enough.>
No "reading between the lines" needed.

0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Resolve Outlook connectivity issues after moving mailbox to new Exchange 2016 server
Use these top 10 tips to master the art of email signature design. Create an email signature design that will easily wow recipients, promote your brand and highlight your professionalism.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now