LessThanDot Site Logo

LessThanDot

A Technical Community for IT Professionals

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Highly Rated Users

Forum
No Posts Rated

Top 50
Given
Received

Links

Wiki
Blog

Forum Statistics

Users
Members:
1882
Members Online:
2
Guests Online:
89

Total Post History
Posts:
81456
Topics:
18718

7-Day Post History
New Posts:
0
New Topics:
0
Active Topics:
0

Our newest member
justanails28

Other

FAQ
All times are UTC [ DST ]

CDO 101 - email code error

Please wait...

CDO 101 - email code error

Postby genomon on Wed Jan 06, 2010 11:14 pm

Access 2000 / CDO 1.21 lib
How simple is it to send email? Apparently not as simple as I would like. I looked at code samples & tutorials and still get error 13 type mismatch on line 13 (address line). Of course this is the line I modified after looking at the property in object browser, as the code samples I found all used adress book entries, and all I need is to add a single address without worrying about an address book. Thought it would be easy - I'm missing something really obvious here. Any glaring irregularities?


  1.    Dim objSession As Object
  2.     Dim objMessage As Object
  3.     Dim objRecipient As Object
  4.  
  5.     Set objSession = CreateObject("mapi.session")
  6.     objSession.Logon
  7.     Set objMessage = objSession.Outbox.Messages.Add
  8.     objMessage.Subject = "My Subject"
  9.     objMessage.Text = "My Message"
  10.     Set objRecipient = objMessage.Recipients.Add
  11.     objRecipient.Name = "Me"
  12.     objRecipient.Type = 1
  13.     objRecipient.Address = "me@me.com"
  14.     objRecipient.Resolve
  15.     objMessage.Send showDialog:=False
  16.  
  17.  
A fronte praecipitium a tergo lupi
User avatar
genomon
Senior Sage
Senior Sage
LTD Silver - Rating: 479LTD Silver - Rating: 479LTD Silver - Rating: 479LTD Silver - Rating: 479LTD Silver - Rating: 479
LTD Silver - Rating: 479LTD Silver - Rating: 479LTD Silver - Rating: 479
 
Posts: 1945
Joined: Fri Feb 01, 2008 5:44 pm
Location: Madison, Wisconsin USA
Unrated

Re: CDO 101 - email code error

Postby gmmastros on Wed Jan 06, 2010 11:25 pm

Just for giggles, try this:

  1. objRecipient.To = "me@me.com"
  2.  
-George
User avatar
gmmastros
LTD Admin
LTD Admin
LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630
LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630
LTD Gold - Rating: 1630LTD Gold - Rating: 1630
 
Posts: 2369
Joined: Tue Oct 09, 2007 5:19 pm
Unrated

Re: CDO 101 - email code error

Postby Emtucifor on Thu Jan 07, 2010 12:08 am

I think George is on the right track. The Address property is probably an object... so either you have to set the To address differently, or you have to provide a proper Address object, which might be able to be created on the fly rather than actually using one from the address book.
God cries a little bit every time someone builds a database.
User avatar
Emtucifor
Guru
Guru
LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033
LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033
LTD Gold - Rating: 1033
 
Posts: 2835
Joined: Fri May 30, 2008 9:30 pm
Location: Bellingham, WA
Unrated

Re: CDO 101 - email code error

Postby Remou on Thu Jan 07, 2010 12:24 am

I have vague memories of trying this and failing to get it to resolve, I found out why at the time, I think, but sadly, cannot remember now. The name needs to be a name in your address book, as far as I recall (http://msdn.microsoft.com/en-us/library/aa480383.aspx). I wonder if RDO is any use to you?
Stop quoting laws to us. We carry swords.
User avatar
Remou
LTD Admin
LTD Admin
LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971
LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971
LTD Gold - Rating: 971
 
Posts: 5414
Joined: Sun Oct 14, 2007 11:26 am
Unrated

Re: CDO 101 - email code error

Postby Emtucifor on Thu Jan 07, 2010 1:06 am

You could try this:

  1. Set CDOMessage = CreateObject("CDO.Message")
  2. With CDOMessage
  3.    .Subject =
  4.    .From =
  5.    .To =
  6.    .Bcc =
  7.    .TextBody = (used in popup preview pane)
  8.    .HTMLBody =
  9.    .Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
  10.    .Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "yourexchangeservername"
  11.    .Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
  12.    .Configuration.Fields.Update
  13.    .Send
  14. End With
God cries a little bit every time someone builds a database.
User avatar
Emtucifor
Guru
Guru
LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033
LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033
LTD Gold - Rating: 1033
 
Posts: 2835
Joined: Fri May 30, 2008 9:30 pm
Location: Bellingham, WA
Unrated

Re: CDO 101 - email code error

Postby Naomi on Thu Jan 07, 2010 4:15 am

This is VFP code, but should be very easy to change for VB

http://www.berezniker.com/content/pages ... l-cdo-2000 (and check the class from that link)
Premature optimization is the root of all evil in programming.
Donald Knuth, repeating C. A. R. Hoare
Naomi
Senior Sage
Senior Sage
LTD Bronze - Rating: 234LTD Bronze - Rating: 234LTD Bronze - Rating: 234LTD Bronze - Rating: 234LTD Bronze - Rating: 234
 
Posts: 1755
Joined: Thu Mar 26, 2009 5:46 am
Location: WI
Unrated

Re: CDO 101 - email code error

Postby Emtucifor on Thu Jan 07, 2010 8:37 am

Naomi,

cdo2000.fxp looks foxpro-specific...
God cries a little bit every time someone builds a database.
User avatar
Emtucifor
Guru
Guru
LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033
LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033
LTD Gold - Rating: 1033
 
Posts: 2835
Joined: Fri May 30, 2008 9:30 pm
Location: Bellingham, WA
Unrated

Re: CDO 101 - email code error

Postby genomon on Thu Jan 07, 2010 3:29 pm

Thanks for all the responses! We're in the middle of a nice average (4 - 6") winter storm here right now so don't know how much longer I'll be here. Still working on my little app and getting closer. Apparently CDO is married to the dang address book no matter what.
That is what ".Resolve" does - resolves the "to" name (alias) to an address in the book. Working on manipulating the book in code right now. George - no love with .To. Emtucifor - great code, still needs addr book for .To to work (so far). Naomi - thanks, but no love there either (definitely VFP code). Remou - your link was quite helpful, and also led me to this: http://support.microsoft.com/kb/172093 . So since it's icky outside here, and hardly anyone else showed up today (gotta love AWD in snow), I have most of the rest of a shift to play with this stuff. Kinda embarrassing since I always thought this stuff was soooo simple that I never took time to play with it before. And like everything else Microsoft, it's perfectly documented :---)
Thanks all - I'll be back with more as I find it, or get any new responses!

EDIT: Just keeps getting more fun. Heck, using the Outlook object model looks even better - found this on Egghead Cafe:
  1. strTo = "ossiemac@ossiemac.com"     'Sample. Not valid email address
  2. strCC = "myfriend@friends.com"         'Sample. Not valid email address
  3. strBCC = "secretfriend@secrets.com"  'Sample. Not valid email address
  4. Set objMail = objOutlook.CreateItem(olMailItem)
  5.  
  6. With objMail
  7. .To = strTo
  8. .CC = strCC
  9. .BCC = strBCC
  10. .Subject = strSubject
  11. .Body = strBody
  12. .DeleteAfterSubmit = True   'Does not save email in Sent mail folder.
  13. .Display
  14. .Send
  15. End With
  16.  
A fronte praecipitium a tergo lupi
User avatar
genomon
Senior Sage
Senior Sage
LTD Silver - Rating: 479LTD Silver - Rating: 479LTD Silver - Rating: 479LTD Silver - Rating: 479LTD Silver - Rating: 479
LTD Silver - Rating: 479LTD Silver - Rating: 479LTD Silver - Rating: 479
 
Posts: 1945
Joined: Fri Feb 01, 2008 5:44 pm
Location: Madison, Wisconsin USA
Unrated

Re: CDO 101 - email code error

Postby gmmastros on Thu Jan 07, 2010 3:51 pm

Ah... I see. The title of this thread suggest that you are using CDO to send emails, but your code is suggesting MAPI. Apples & Oranges.

  1. CreateObject("mapi.session")
  2.  


My earlier advice was based on the assumption that you were using CDO, so of course it wouldn't work.

The following code is what I use on my web sites (written in classic ASP). In this case, it should be very easy to convert it to VBA.

  1. Function SendCDOEmail_Direct(smtpserver,username,password, strSubject, strBody,strFromAddress,strToAddress,strCc, intpriority, strBodyFormat)
  2.  
  3.     Dim objCDOSysCon, objCDOSysMail
  4.    
  5.     Set objCDOSYSMail = Server.CreateObject("CDO.Message")
  6.     Set objCDOSYSCon = Server.CreateObject("CDO.Configuration")
  7.    
  8.     If smtpserver = "" Then
  9.         smtpserver = "(default smtp server here)" 'smtp.gmail.com
  10.     End If
  11.    
  12.     If username = "" Then
  13.         username = "(default user name here)"
  14.     End If
  15.    
  16.     If password = "" Then
  17.         password = "(default password here)"
  18.     End If
  19.    
  20.     With objCDOSYSCon
  21.         .Fields("http://schemas.microsoft.com/cdo/configuration/smtpserver") = smtpserver
  22.         .Fields("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
  23.         .Fields("http://schemas.microsoft.com/cdo/configuration/enablessl") = 1
  24.         .Fields("http://schemas.microsoft.com/cdo/configuration/sendusername") = username
  25.         .Fields("http://schemas.microsoft.com/cdo/configuration/sendpassword") = password
  26.        
  27.         .Fields("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465
  28.         .Fields("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
  29.         .Fields("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = 1
  30.         .Fields("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 120
  31.         .Fields.Update
  32.     End With
  33.    
  34.     Set objCDOSYSMail.Configuration = objCDOSYSCon
  35.  
  36.     With objCDOSYSMail
  37.         '.Fields("urn:schemas:httpmail:importance").Value = intpriority '0=low, 1=normal, 2=high
  38.         .From = strFromAddress
  39.         .To = strToAddress
  40.         ' .Cc = strCc
  41.         .subject = strSubject
  42.         .BCC = strCc
  43.  
  44.         If ucase(strBodyFormat) ="HTML" Then
  45.             .HTMLBody = strBody
  46.         Else
  47.             .textbody = strbody
  48.         End If
  49.        
  50.         .Fields.Update
  51.        
  52.         On Error Resume Next
  53.        
  54.         .Send
  55.         SendCDOEmail = Err.Description
  56.        
  57.     End With
  58.    
  59.     Set objCDOSysMail = nothing
  60.     Set objCDOSysCon = nothing
  61.  
  62.     SendCDOEmail_Direct = Message
  63.  
  64. End Function
  65.  


Note that I am using a gmail account to send my emails from. This explains why the smtpserverport = 465 and also the smptusessl, and some of the other settings.

I don't know if this will help you, but I hope it does.
-George
User avatar
gmmastros
LTD Admin
LTD Admin
LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630
LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630
LTD Gold - Rating: 1630LTD Gold - Rating: 1630
 
Posts: 2369
Joined: Tue Oct 09, 2007 5:19 pm
Unrated

Re: CDO 101 - email code error

Postby genomon on Thu Jan 07, 2010 4:03 pm

Thanks, George. I'm new to the email thing - as for the apples/oranges, I added a VBA reference to the CDO 1.21 lib, and lo & behold when I look in object browser it's MAPI. Since I pretty much have some time today, I'll try your stuff (looks good), and continue learning the Outlook object model, which because of it's simplicity, is looking good as well. We're switching to Exchange Server this year, so I'm assuming I'll end up a sage CDO guru by 2011 (or more likely, die trying). ;)
A fronte praecipitium a tergo lupi
User avatar
genomon
Senior Sage
Senior Sage
LTD Silver - Rating: 479LTD Silver - Rating: 479LTD Silver - Rating: 479LTD Silver - Rating: 479LTD Silver - Rating: 479
LTD Silver - Rating: 479LTD Silver - Rating: 479LTD Silver - Rating: 479
 
Posts: 1945
Joined: Fri Feb 01, 2008 5:44 pm
Location: Madison, Wisconsin USA
Unrated

Re: CDO 101 - email code error

Postby Remou on Thu Jan 07, 2010 4:04 pm

If all you want to do is send emails with Outlook and Access, that is dead easy, except for the complaints about security. If these bother you, you can use RDO. I have written a little in the Wiki about Access & Email. If you intend this thing to last past Access 2010, CDO is not such a good idea.
Stop quoting laws to us. We carry swords.
User avatar
Remou
LTD Admin
LTD Admin
LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971
LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971
LTD Gold - Rating: 971
 
Posts: 5414
Joined: Sun Oct 14, 2007 11:26 am
Unrated

Re: CDO 101 - email code error

Postby genomon on Thu Jan 07, 2010 4:14 pm

If you intend this thing to last past Access 2010, CDO is not such a good idea.


Thanks, Remou. I'm having a blast with this stuff so far. Love new stuff (to me), and there's lots more of it than I suspected. As far as Email from Access proper, I need to add multiple attachments, so .SendObject isn't an option. I haven't gotten real far in e-education yet (two days so far); what will play nicely with Exchange? Apps won't all be in Access, we have some VB6 stuff I will be migrating to something.net as well this fall or next spring.
A fronte praecipitium a tergo lupi
User avatar
genomon
Senior Sage
Senior Sage
LTD Silver - Rating: 479LTD Silver - Rating: 479LTD Silver - Rating: 479LTD Silver - Rating: 479LTD Silver - Rating: 479
LTD Silver - Rating: 479LTD Silver - Rating: 479LTD Silver - Rating: 479
 
Posts: 1945
Joined: Fri Feb 01, 2008 5:44 pm
Location: Madison, Wisconsin USA
Unrated

Re: CDO 101 - email code error

Postby Remou on Thu Jan 07, 2010 4:36 pm

I do not mean .Sendobject I mean use Outlook.

  1. Function EmailAttach(strEmail, strAttach) As Boolean
  2. Dim olApp As Object 'Outlook.Application
  3. Dim olItem As Object 'Outlook.MailItem '
  4. Dim olNS As Object 'Outlook.NameSpace
  5. Dim blnStart As Boolean
  6. Dim olFolder As Object 'Outlook.Folders
  7.  
  8. 'http://msdn2.microsoft.com/en-us/library/aa217214(office.10).aspx
  9. 'objAttachments.Add(Source, [Type], [Position], [DisplayName])
  10.  
  11. '4 olByReference Create a shortcut to an external file
  12. '1 olByValue Embed attachment in the item
  13. '5 olEmbeddedItem Create a shortcut to an Outlook item
  14.  
  15.     EmailAttach = False
  16.    
  17.     Set olApp = CreateObject("Outlook.Application")
  18.  
  19.  
  20.     Set olNS = olApp.GetNamespace("MAPI")
  21.     Set olFolder = olNS.GetDefaultFolder(16) 'olFolderDrafts
  22.     Set olItem = olApp.CreateItem(0)  'olMailItem
  23.     With olItem
  24.         .Subject = "InvoiceSubject"
  25.         .HTMLBody = "InvoiceOutlineText"
  26.         .To = strEmail
  27.         .Save
  28.         .Attachments.add strAttach, 4, , "Invoice"
  29.         .Save
  30.     End With
  31.  
  32.     Set olItem = Nothing
  33.  
  34.     EmailAttach = True
  35.  
  36.  
  37. End Function
Stop quoting laws to us. We carry swords.
User avatar
Remou
LTD Admin
LTD Admin
LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971
LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971
LTD Gold - Rating: 971
 
Posts: 5414
Joined: Sun Oct 14, 2007 11:26 am

Re: CDO 101 - email code error

Postby genomon on Thu Jan 07, 2010 4:47 pm

Nice! Thanks, and a +10 (thankfully we're out of little pink stars over here).
A fronte praecipitium a tergo lupi
User avatar
genomon
Senior Sage
Senior Sage
LTD Silver - Rating: 479LTD Silver - Rating: 479LTD Silver - Rating: 479LTD Silver - Rating: 479LTD Silver - Rating: 479
LTD Silver - Rating: 479LTD Silver - Rating: 479LTD Silver - Rating: 479
 
Posts: 1945
Joined: Fri Feb 01, 2008 5:44 pm
Location: Madison, Wisconsin USA
Unrated

Re: CDO 101 - email code error

Postby genomon on Thu Jan 07, 2010 5:53 pm

A lot of learning and research for a few lines of code. Finally, this is what works (condensed down - there is looping involved for addresses and attachments in my app). I still have a ton of research to do regarding all the great suggestions above (and am looking forward to it) - Thanks, All!

:salute:

  1. Dim objOutlook As Object
  2. Dim objMail As Object
  3. Dim strTo As String
  4.  
  5. strTo = "me@me.com"
  6. Set objOutlook = Outlook.Application
  7. Set objMail = objOutlook.CreateItem(olMailItem)
  8.  
  9. With objMail
  10.     .To = strTo
  11.     .Subject = "Testing"
  12.     .Body = "Test msg."
  13.     .Attachments.Add "C:\pathname1"
  14.     .Attachments.Add "C:\pathname2"
  15.     .DeleteAfterSubmit = False
  16.     .Display
  17.     .Send
  18. End With
  19.  
  20. Set objMail = Nothing 
  21. Set objOutlook = Nothing
A fronte praecipitium a tergo lupi
User avatar
genomon
Senior Sage
Senior Sage
LTD Silver - Rating: 479LTD Silver - Rating: 479LTD Silver - Rating: 479LTD Silver - Rating: 479LTD Silver - Rating: 479
LTD Silver - Rating: 479LTD Silver - Rating: 479LTD Silver - Rating: 479
 
Posts: 1945
Joined: Fri Feb 01, 2008 5:44 pm
Location: Madison, Wisconsin USA
Unrated

Re: CDO 101 - email code error

Postby Emtucifor on Thu Jan 07, 2010 6:00 pm

genomon wrote:Emtucifor - great code, still needs addr book for .To to work (so far).

That doesn't make sense to me. What I posted was basically straight out of a working ASP web page... no Outlook even present on the computer it was running from.
God cries a little bit every time someone builds a database.
User avatar
Emtucifor
Guru
Guru
LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033
LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033LTD Gold - Rating: 1033
LTD Gold - Rating: 1033
 
Posts: 2835
Joined: Fri May 30, 2008 9:30 pm
Location: Bellingham, WA
Unrated

Re: CDO 101 - email code error

Postby Remou on Thu Jan 07, 2010 6:01 pm

Best to save before adding attachments.
Stop quoting laws to us. We carry swords.
User avatar
Remou
LTD Admin
LTD Admin
LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971
LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971LTD Gold - Rating: 971
LTD Gold - Rating: 971
 
Posts: 5414
Joined: Sun Oct 14, 2007 11:26 am
Unrated

Re: CDO 101 - email code error

Postby genomon on Thu Jan 07, 2010 6:09 pm

That doesn't make sense to me. What I posted was basically straight out of a working ASP web page


In my ignorance I tried to copy/paste into my code the methods that looked relevant and of course that didn't work (for me). I'm going back thru all the great stuff you, George, & Remou posted after I get this rascal rolled out. I have lots to learn here yet!

R - thanks for the save hint...
A fronte praecipitium a tergo lupi
User avatar
genomon
Senior Sage
Senior Sage
LTD Silver - Rating: 479LTD Silver - Rating: 479LTD Silver - Rating: 479LTD Silver - Rating: 479LTD Silver - Rating: 479
LTD Silver - Rating: 479LTD Silver - Rating: 479LTD Silver - Rating: 479
 
Posts: 1945
Joined: Fri Feb 01, 2008 5:44 pm
Location: Madison, Wisconsin USA
Unrated