access vba smtp and undesired multiple attachments 2

[boggsie]

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:

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("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/sendusing")[/URL] = 2
ObjMessage.Configuration.Fields.Item("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/smtpserver")[/URL] = sMailServer
ObjMessage.Configuration.Fields.Item("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/smtpserverport")[/URL] = 25
ObjMessage.Configuration.Fields.Update
ObjMessage.Send

Recordset.MoveNext
Loop
Recordset.Close

End Function

 

[oharab]

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:

'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

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("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/sendusing")[/URL] = 2
        .Configuration.Fields.Item("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/smtpserver")[/URL] = sMailServer
        .Configuration.Fields.Item("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/smtpserverport")[/URL] = 25
        .Configuration.Fields.Update
        .Send
    End With
End Sub

for each mail to send.

hth

Ben

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

We could be confused in exactly the same way, but confusion might be like Nulls, and not comparable.

 

[boggsie]

Ok, so what is your PayPal address?

Magnificent ... Thank you SO VERY MUCH!!

Best regards,
-boggsie

 

[oharab]

You're very welcome.

B.

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

We could be confused in exactly the same way, but confusion might be like Nulls, and not comparable.

 

[boggsie]

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.

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

 

[PHV]

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

 

[boggsie]

Giddy excited laughter ensues !!!

THANK YOU!!

Best regards,
-boggsie