Solved

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

Posted on 2008-06-26
29
1,306 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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
 
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

SuperAntiSpyware Licenses Discounted by 25% !

Exclusive offer to Experts Exchange Members!
Buy SuperAntiSpyware License(s) from us and save 25% on the regular purchase price.
- Includes Full SuperAntiSpyware Vendor Support Entitlements
- Your Subscription does not begin until you activate your license
- Buy for your friends

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

739 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