Solved

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

Posted on 2008-06-26
29
1,284 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
ID: 21881066
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
ID: 21881527
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
ID: 21884697
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
ID: 22388351
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
ID: 22420472
Vee,
Can you add the Exchange Zone?
Jeff
0
 
LVL 61

Expert Comment

by:mbizup
ID: 22422563
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
ID: 22424327
LOL,

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

Jeff
0
 
LVL 9

Author Comment

by:Sar1973
ID: 22427261
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
ID: 22428155
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
ID: 22432787
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
ID: 22435731
-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
ID: 22445910
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 9

Author Comment

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

Author Comment

by:Sar1973
ID: 22449569
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
ID: 22459765
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
ID: 22460701
0
 
LVL 61

Expert Comment

by:mbizup
ID: 22461380
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
ID: 22462625
Just explain how to raise the points assigned to this question, or do it yourself.
0
 
LVL 9

Author Comment

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

Expert Comment

by:mbizup
ID: 22498019
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
ID: 22503020
I finally found it: .SentOnBehalfOfName = "address@email.it"
0
 
LVL 9

Author Comment

by:Sar1973
ID: 22503030
No solution, I found it elsewhere.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 22504233
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
ID: 22508623
Maybe, between the lines.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 22509682
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Follow this checklist to learn more about the 15 things you should never include in an email signature from personal quotes, animated gifs and out-of-date marketing content.
This article lists the top 5 free OST to PST Converter Tools. These tools save a lot of time for users when they want to convert OST to PST after their exchange server is no longer available or some other critical issue with exchange server or impor…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Many of my clients call in with monstrous Gmail overloading issues with Outlook. A quick tip is to turn off the All Mail and Important folders from synching. Here is a quick video I made to show you how to turn off these and other folders in Gmail s…

910 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

24 Experts available now in Live!

Get 1:1 Help Now