Solved

eMail attachments through Access VBA

Posted on 2008-11-02
29
680 Views
Last Modified: 2013-12-18
Dear experts,

I have a folder full of Excel files, each meant to be eMailed to different people. Identifying who gets which file is easy. I will appreciate a code sample that walks through how to pick a file from the file system, attach it to an eMail and send out the Email with a 'To' address and a 'Cc'.

Our company typically uses Outlook as the eMail client... but if the solution is easier/more robust, in some other non-commercial technology, that is ok too.

This is a small part of a larger Access based application. So, even if there are better ways of getting this done outside of Access VBA, I will prefer that it be implemented in Access VBA.

Regards,
Rajesh
0
Comment
Question by:kavirajesh
[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
  • 14
  • 13
  • 2
29 Comments
 
LVL 18

Expert Comment

by:jmoss111
ID: 22862474
Example using CDO
Public Sub Email270DayHolds()
Dim Db As DAO.Database
Dim ThsDay As String
Dim ThisDay As String
Dim ThisYY As String
Dim ThisMM As String
Dim ThisDD As String
Dim strTo As String
Dim strCC As String
Dim strBCC As String
Dim strFrom As String
Dim strTitle As String
Dim rs As Recordset
Dim txtAttach1 As String
Dim txtAttach2 As String
Dim strSubj As String
Dim strBody As String
Dim strNoAddr As String
Dim strSupplier As String
Dim strDate As String
Dim cdoMessage As Object
Dim objCDOMail As Object
Dim strschema As String
'On Error GoTo ErrorHandler ' Enable error-handling routine.
 
 
 
'===================================================================================================
Set Db = CurrentDb
          ThisDay = Date$
          ThsDay = Right([ThisDay], 4) & Left([ThisDay], 2) & Mid([ThisDay], 4, 2)
          strDate = ThsDay
          
          Set rs = Db.OpenRecordset("qrsEmailWithSubjAndBody")
'===================================================================================================
        
          rs.MoveFirst
          Do While Not rs.EOF
            
            strTo = rs!EmailAddr
             sTo = strTo
            strFrom = rs!myemailaddr
            
            'End If
            strSubj = rs!SubjectLine
            strBody = rs!BodyContents
                                  txtAttach2 = "C:\Acct\IC_Data\270Days\HoldsGreaterThan270DaysAtEndOfMonthSummary_" & strDate & ".xls"
            txtAttach1 = "C:\Acct\IC_Data\270Days\HoldsGreaterThan270DaysAtEndOfMonthDetails_" & strDate & ".xls"
                  '---------------------------------------------------------------------------------
             sPathFile = txtAttach1
          ' Create the message.
          
                      
                  '===================================================================================================
                Set cdoMessage = CreateObject("CDO.Message")
                Set objCDOMail = CreateObject("CDO.Configuration")
                strschema = "http://schemas.microsoft.com/cdo/configuration/"
                objCDOMail.Load -1 ' CDO Source Default
                'If you have illegal or wrong smtp address here it will run for 30-60
                'seconds and finally give transport error
                With objCDOMail.Fields
                    .Item(strschema & "sendusing") = 2 ' cdoSendUsingPort
                    .Item(strschema & "smtpserver") = "mymailhub.com" ' "Your SMTP server address here"
                    .Item(strschema & "smtpserverport") = 25 'specify port number
                    .Item(strschema & "smtpconnectiontimeout") = 120
                    .Update
                End With
 
                With cdoMessage
                    Set .Configuration = objCDOMail
                        .To = strTo
                        .FROM = strFrom
                        .CC = "me@mail.com"
                        '.Bcc = "me@mail.com"
                        .Subject = strSubj
                        .TextBody = strBody
                        .AddAttachment txtAttach1
                        .AddAttachment txtAttach2
                        .send
                End With
                 Db.Execute ("qriEmailLog")
                 sTo = ""
                
                strTo = ""
                strCC = ""
                strBCC = ""
          rs.MoveNext
          Loop
Set cdoMessage = Nothing
Set objCDOMail = Nothing
Exit Sub ' Exit to avoid handler.
ErrorHandler: ' Error-handling routine.
Debug.Print Err.Number & "-" & Err.Description
Set cdoMessage = Nothing
Set objCDOMail = Nothing
 
End Sub

Open in new window

0
 

Author Comment

by:kavirajesh
ID: 22862486
Hi jmoss,

Thanks for the quick response.

I did see a reference to CDO Mail on experts... I have never heard of this, though. Any good links that can throw light on the reliability and an overview of the same?

Regards,
Rajesh
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 22862537
Hello kavirajesh,

CDO is an accepted way to do it.  I do not use that method myself, but do not take that as a criticism.

If you use Outlook, and would prefer to automate Outlook to do this, the snippet below illustrates how.
Be aware, though, that automating Outlook will trigger security warnings that may annoy you.  To deal
with the warnings, you can use:

1) Express ClickYes
http://www.contextmagic.com/express-clickyes/
There is a free version and a Pro version for ~$30 with more features.  I've used the pro version.
It works as advertised.  I'm not using it currently.  I'm using the MAPILab product instead.

2) MAPILab Advanced Outlook Security
http://www.mapilab.com/outlook/security/
A free COM add-in for Outlook.  I am using it now and I like it

3) Redemption
http://www.dimastr.com/redemption/
A DLL you can use in your code to disable the warnings.  Free download, but you must pay a
license to distribute.  I've used it and like it.

Regards,

Patrick
Dim olApp As Object
Dim olMsg As Object 
Set olApp = CreateObject("Outlook.Application")
Set olMsg = olApp.CreateItem(0)
With olMsg
    .To = "X@z.com"
    .CC = "y@z.com"
    .BCC = "z@z.com"
    .Subject = "subject"
    .Body = "body"
    .Attachments.Add "c:\folder\subfolder\foo.xls"
    .Send
End With 
Set olMsg = Nothing
Set olApp = Nothing

Open in new window

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 18

Expert Comment

by:jmoss111
ID: 22862564
The reason that I use CDO is that it bypasses the security problems of Outlook; the problem of your sent message box getting full from all the messages, ability to use another sending email address with ease. If you have a mail server then you most likely have an SMTP server which CDO uses.

0
 

Author Comment

by:kavirajesh
ID: 22862570
Thanks Patrick. I did read an earlier post from you which spoke of the security concerns and gave the 3 options... but I did not see the Outlook code there..

I am going to put both of these together and see if it works for me.

Couple of small questions on this
1) Will this leave a copy of the message in Sent Items?
2) Will the recipient be able to do a "Reply-To" on this message if required?
3) Is there a limitation on the attachment size?
4) There seems to be no exception handling of any sort happening in the code snippet you have given... not necessary? Haven't done eMail automation before... hence all these q's!

Thanks for the response. It is 12AM where I come from... I dont know how much longer I am going to be on. Will get back on this asap.

Regards,
Rajesh
0
 
LVL 18

Expert Comment

by:jmoss111
ID: 22862593
Mail via CDO has been around for quite awhile and is very reliable. My apps send around 15,000 to 20,000 emails a week using this method and have for years without a hitch.

0
 

Author Comment

by:kavirajesh
ID: 22862617
Hi jmoss,

I understand that you have added in db's and recordsets and stuff to get the data from... but it does make the code sound awfully long!!

Would it be possible for you to assume that I will figure out how to get the raw data for each of the "To", "Cc", "Subject" etc. and just give me only the CDO part of the code?

I took one look at it and said, "Hey! 75 lines of code, with YY, MM and DD does not seem necessary for sending out a mail!!" :-)

Also, as requested in my earlier post, can you point me in the direction of some informative, sound link on CDO?

This is quite urgent (which explains the 500 points :-)

Regards,
Rajesh
0
 

Author Comment

by:kavirajesh
ID: 22862639
Patrick,

Is the MAPI add-in stable with Outlook 2007?

One thing missing in your solution was the From address? Right now, I dont have access to an SMTP or I would have tested it out. Can I use any "From" address and send out the message? I have multiple accounts configured in my Outlook. Which of those will get picked? Little confused!

Regards,
Rajesh
0
 

Author Comment

by:kavirajesh
ID: 22862665
Hi jmoss,

What would the references be for CDO?

Regards,
Rajesh
0
 
LVL 18

Accepted Solution

by:
jmoss111 earned 500 total points
ID: 22862670
The ISO date YYYYMMDD appended to the end of the mail is what decides whic attachemnt to add; this is done weekly and its just for those specific files. You may not need that in your implementation. My example show selecting a file, you mentioned using file system. I don't incorporate using fso into my email.

The minimum code for cbd in vba:

Dim cdoConfig
Dim msgOne

Set cdoConfig = CreateObject("CDO.Configuration")
With cdoConfig.Fields
.Item(cdoSendUsingMethod) = cdoSendUsingPort
.Item(cdoSMTPServerPort) = (yourport number) usually is 25
.Item(cdoSMTPServer) = "mail.yourmailserver.com"
'.Item(cdoSendUserName) = "myName"
'.Item(cdoSendPassword) = "myPassword"
.Update
End With

Set msgOne = CreateObject("CDO.Message")
Set msgOne.Configuration = cdoConfig
msgOne.To = "name@yourdomain.com"
msgOne.From = "name@yourdomain.com"
msgOne.Subject = "Test"
msgOne.TextBody = "It work just fine"
msgOne.Send

For links about CDO, you can google as easily as I can; and if you google you're going to find tons of links.

0
 
LVL 18

Expert Comment

by:jmoss111
ID: 22862682
You may be better off using Patrick's method; I'd stay away from click yes though, because it's way too slow.
0
 
LVL 18

Expert Comment

by:jmoss111
ID: 22862703
kavirajesh,

There is no reference, you're calling all you need in the vba

Jim
0
 

Author Comment

by:kavirajesh
ID: 22862716
jmoss,.

I was thinking on the lines of giving a shot on both CDO and what Patrick's said. Why would you think Patrick's is better?

And Google did throw up tons of links on CDO :-) which is why I wanted to know if there were any sites which had good,solid, current info. Skip that request though. I will figure it out.

Regards.
Rajesh

PS: And I figured for References, I will need to add Microsoft CDO for Windows 2000.
0
 

Author Comment

by:kavirajesh
ID: 22862769
Jim,

I put in the data that is relevant to me, and in the .send, I am getting this error message

"The server rejected one or more recipient addresses.The server response was 550 to Relay."

I am assuming the line
.Item(cdoSendUsingMethod) = cdoSendUsingPort
must be left as is? I am assuming cdoSendUsingPort is some constant.

Regards,
Rajesh
0
 

Author Comment

by:kavirajesh
ID: 22862816
Jim,

My mistake, I guess. It worked now. I was using the outgoing SMTP mail server that my mail service provider had given me... it didnt work... I tried my Incoming Mail Server, and it worked. :-)

I will try the attachment bit, and let you know.

I am curious to understand why you said Patrick's solution was better? I am sure his solution will work too. Given the amount of time you have spent on this,and the fact that it works, I would definintely want to accept your solution. That said, if Pat's is better, then I would pick that (if it works painlessly) and accept multiple solutions.

Regards,
Rajesh
0
 
LVL 18

Expert Comment

by:jmoss111
ID: 22862866
I didn't say better, but maybe easier to understand for you to get up and running quicker. My method has no references, no need for security workarounds, is more versatile, but maybe a bit more challenging. And if you're under the gun to get working maybe it's the way to go for you. I would never use Outlook but to each his own.

Regards,

Jim
0
 
LVL 18

Expert Comment

by:jmoss111
ID: 22862887
I couldn't give you any good links for CDO because once I got it running I've had no problem. I've been using CDO for about eight years so any links I had would be lost by now. Microsoft would be a good place to search though.
0
 
LVL 18

Expert Comment

by:jmoss111
ID: 22862912
The 550 may be thrown due to bad email address; using port is 2 for relay use 1. I use 2
0
 

Author Comment

by:kavirajesh
ID: 22862918
Jim,

mmm...  I have got attachments pretty much working with CDO and mails going where I want them to. So I am kinda assuming I have the complexity part worked out for now.

The key questions remaining on CDO are
1) What kind of attachment sizes are permitted?
2) Will a Reply-To be possible for the recipient?
3) Does CDO have an option that helps in zipping up attachments?
4) Also, a one-liner eMail came up with 5KB, Why is that?

I am not stuck to Outlook like I mentioned in my original post... since my company is a Microsoft Gold Certified Partner, it just seems to be disloyal not to consider Outlook :-)

Regards,
Rajesh

PS: I took out the Microsoft CDO For Windows 2000 reference and it bombs at the cdoSendUsingPort line... I put it back on, and it works like a charm.
PPS: I really appreciate the speed with which you are getting back to me... looks more like a phone call than a thread to me :-)
0
 
LVL 18

Expert Comment

by:jmoss111
ID: 22862979
1. I routinely send files in excess of 8mb zipped
2. Reply to affirmative. Your sent from should be set up to receive mail.
3. I have a module that zips using 7zip
4. I have no idea

CDO is part of Microsoft OS; I just don't like using it for all my aforementioned reasons, none the least of which is security and the inability of Outlook not being able to use another from address. I love Microsoft products I make a nice living with their products.
0
 

Author Comment

by:kavirajesh
ID: 22863002
1) Great!
2) Cool.
3) can you shed a little more light please? I have 7-zip... or winzip... or Filzip...
4) The question was with specific reference to the test mails I sent using CDO.. it wasnt a generic question :-) Mail size is of particular concern to me, since the recipients may have restricted bandwidths...

Regards,
Rajesh
0
 
LVL 18

Expert Comment

by:jmoss111
ID: 22863131
I really don't understand, email came up with 5k... is 5k larger than email sent from Outlook? I never really cared about small message only file sizes, I would have thought that it would be equivalent to Outlook.
0
 
LVL 18

Expert Comment

by:jmoss111
ID: 22863143
My mailing are generated in steps. Build Excel files, zip excel files, mail zip file and log the mailing.
0
 

Author Comment

by:kavirajesh
ID: 22863185
Jim,

 I never have checked email sizes of small mails either which is why I am surprised that Outlook also throws about the same size for a one-liner eMail. Nothing to do with CDO I guess.

The question of zipping still remains... I will do it in a separate thread.... hope you will be able to share some of that code with me.

I think I am going to stop this thread right about here! Thanks for all the help, Jim!!

Warm Regards,
Rajesh
0
 
LVL 18

Expert Comment

by:jmoss111
ID: 22863222
You're welcome Rajesh.

I'm pretty sure that I have the zip module here at home; it does use 7zip though.

Jim
0
 

Author Comment

by:kavirajesh
ID: 22863262
7-zip is just fine... if you find the module, do remember to post it!

Regards,
Rajesh
0
 
LVL 18

Expert Comment

by:jmoss111
ID: 22863354
I already looked and thought that it was here but I don't have that project here on this system.
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 22863361
Rajesh,

I'm glad you got the help you needed.  The CDO approach is time tested, and people who use it swear by
it.  I haven't used it, but that's just force of habit than anything else.

I have not tested the MaoiLab add-in with Outlook 2007.  It works well with Outlook 2003, and I also do some
development work with Redemption.

Cheers,

Patrick
0
 

Author Comment

by:kavirajesh
ID: 22863461
Thanks for the support, Patrick. I have got the CDO thing working just fine... the entire requirement is done.... I am sure I would have taken a day or two longer if the kind of support that experts-exchange offers, wasnt around!

Warm Regards,
Rajesh
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

696 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