Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login
Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

LINK TO THIS FORUM!

Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

Tek-Tips LinkedIn® Group

Partner With Us!

"Best Of Breed" Forums Add Stickiness To Your Site
Partner Button
(Download This Button Today!)

Feedback

"...I'm a freelance consultant, and your site's helped me with many issues. I just wanted you to know that someone does appreciate the intelligent help your site offers."
More...

Geography

Where in the world do Tek-Tips members come from?
Click Here To Find Out!

Tek-Tips Shirts!

Get Tek-Tips Swag
Get your
Tek-Tips Forums
SWAG here!
Home > Forums > Programmers > DBMS Packages > Microsoft: Access Modules (VBA Coding) Forum

access vba smtp and undesired multiple attachmentsHelpful Member!(2) 

thread705-1680781
Read More Threads Like This One
boggsie (TechnicalUser)
17 Apr 12 22:39
Hello folks!!

I have a very odd issue.

I am using the code to successfully send smtp out of Access 2007.

I establish a connection with the local Access table to pull in the attributes of the message.

One of the attributes is a path and file name to an attachement.

Everything sends fine; no problem with the send.

The problem is that each line of data has a unique attachemnt; a unique file name.

Message loop one gets file A.
Message loop two gets file A and file B.
Message loop three gets file A, file B and file C.

All other attributes are pulled in unique to the associated row or records.  The only issue I am experiencing is associated to the attachements.

here is my code:

CODE

Public Function SendDashboardMail()

sMailServer = "some mail server"
sMailFromAddress = "some mail address"

Set ObjMessage = CreateObject("CDO.Message")
Set Recordset = CurrentDb.OpenRecordset("tblDASHBOARD_DISTRIBUTION")
Set sToAddress = Recordset.Fields("sMailToAddress")
Set sCCAddress = Recordset.Fields("sMailCC")
Set sSubject = Recordset.Fields("sMailSubject")
Set sBody = Recordset.Fields("sMailBody")
Set sMailAttachment = Recordset.Fields("sProjLabel")

Do Until Recordset.EOF

ObjMessage.Subject = sSubject
ObjMessage.From = sMailFromAddress
ObjMessage.To = sToAddress
ObjMessage.CC = sCCAddress
ObjMessage.TextBody = sBody
ObjMessage.AddAttachment sMailAttachment
ObjMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
ObjMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = sMailServer
ObjMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
ObjMessage.Configuration.Fields.Update
ObjMessage.Send

Recordset.MoveNext
Loop
Recordset.Close

End Function
oharab (Programmer)
18 Apr 12 5:35
The problem is you're not creating a new message each time, so your email is just getting appended to.
I suggest you split your code into two seperate pieces:

CODE

'If these aren't changing, make them constants
Const sMailServer = "some mail server"
Const sMailFromAddress = "some mail address"

Public Function SendDashboardMail()
    Dim Recordset As DAO.Recordset
    Set Recordset = CurrentDb.OpenRecordset("tblDASHBOARD_DISTRIBUTION")
    
    'Loop through the recordset, sending the emails
    Do Until Recordset.EOF
        SendMail Recordset.Fields("sMailToAddress"), _
                 Recordset.Fields("sMailCC"), _
                 Recordset.Fields("sMailSubject"), _
                 Recordset.Fields("sMailBody"), _
                 Recordset.Fields("sProjLabel")
        Recordset.MoveNext
    Loop
    Recordset.Close
End Function

which loops through the recordset calling

CODE

Public Sub SendMail(sToAddress, sCCAddress, sSubject, sBody, sMailAttachment)
    Dim objMessage As Object
    Set objMessage = CreateObject("CDO.Message")
    With objMessage
        .Subject = sSubject
        .From = sMailFromAddress
        .To = sToAddress
        .CC = sCCAddress
        .TextBody = sBody
        .AddAttachment sMailAttachment
        .Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
        .Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = sMailServer
        .Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
        .Configuration.Fields.Update
        .Send
    End With
End Sub

for each mail to send.

hth

Ben

----------------------------------------------
Ben O'Hara

Quote (David W. Fenton):

We could be confused in exactly the same way, but confusion might be like Nulls, and not comparable.
  
boggsie (TechnicalUser)
18 Apr 12 7:32
Ok, so what is your PayPal address?

Magnificent ... Thank you SO VERY MUCH!!

Best regards,
-boggsie
Helpful Member!  oharab (Programmer)
18 Apr 12 7:57
You're very welcome.

B.

----------------------------------------------
Ben O'Hara

Quote (David W. Fenton):

We could be confused in exactly the same way, but confusion might be like Nulls, and not comparable.
  
boggsie (TechnicalUser)
18 Apr 12 8:28
Ok, thank you sir ... and, of course, a follow up.

Now that you've shown me the way to get all of the items for a single message directly from the database, I need to get somewhat crafty with one of the elements, which happens to be a dynamically produced file.

The file syntax is fixed, it includes an attribute "sProjLabel" and a date format.

CODE

C:\Data\_DashboardReporting\_ReportOutput\" + sLabel + "_Dashboards_" + Format(Date, "YYYY-MM-DD") + ".xls"

So, the sMailAttachment is actually not a hard-coded path/file from the database (I set it to this for testing / ease of explanation).

Thus, the progression is to get "sProjLabel" from the database and insert it into the code above, along with the dynamically created date format.

I hope this makes some sense ...

TIA !!

Best regards,
-boggsie
Helpful Member!  PHV (MIS)
18 Apr 12 8:44
Replace this:
Recordset.Fields("sProjLabel")
with this:
"C:\Data\_DashboardReporting\_ReportOutput\" & Recordset!sProjLabel & "_Dashboards_" & Format(Date, "YYYY-MM-DD") & ".xls"

Hope This Helps, PH.
FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

boggsie (TechnicalUser)
18 Apr 12 9:08
Giddy excited laughter ensues !!!

THANK YOU!!

Best regards,
-boggsie
Read More Threads Like This One

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Back To Forum

Back To Microsoft: Access Modules (VBA Coding)

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close