Solved

eMail attachments through Access VBA

Posted on 2008-11-02
29
671 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
  • 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
 
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Suggested Solutions

Easy CSR creation in Exchange 2007,2010 and 2013
Following basic email etiquette rules will help you write a professional email and achieve a good, lasting impression with your contacts.
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…
To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…

758 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

22 Experts available now in Live!

Get 1:1 Help Now