Two snapshots in one email message?

[bmurch71]

Hi,
I'm using the sendobject command in a macro to email snapshots of two reports. I'd prefer to have both reports added as attachments to the same email message, but I can't figure out how to make this happen. I tried seperating the report names with a semi-colon in the object name box of the macro design, but Access didn't like that. As it is right now, I can only seem to get it to send 2 seperate email messages with one attachment each. Any ideas? Thanks v. much in advance for help!
Brian

 

[Davers]

How about something like this:

Dim myDir As String
Dim myDir2 As String

myDir = W:\RCBWPA\DaveDailyTasks\StatusReportv3.xls
myDir2 = W:\RCBWPA\ArchivedData\UserInfo.txt

MailItem.Attachments.Add myDir
MailItem.Attachments.Add myDir2

I use this to attach 2 files to my e-mail using Excel. I haven't tested it in Access...but I'm guessing it would work.

Hope this helps,

Dave

 

[Davers]

I just tested it out and it worked for me in Access as well...let me know if it works for you too!

Good luck,

Dave

 

[bmurch71]

Ok, so now I must admit to my ignorance. Where do I need to go to use the code you wrote? I know almost nothing about VB, so I'm kind of at a loss here. Your code seemed to point to file locations for the reports to send out, but I was relying on Access to create the snapshot version of the report (within the macro I wrote), so I don't know where they (the snapshots) live. Any ideas on that one (or am I completely off base)? Thanks for any advice/help -
Brian

 

[Davers]

Oopsie! Sorry, I assumed you were saving the snapshots in a directory and you had code to send the e-mails....sorry about that!! Do you know a little bit about Visual Basic for Access? Do you know how to create a module? Let me know if you'd like me to post the code to first save the file to a directory you choose as a .SNP (snapshot) file, and then the code that will create the e-mail and attach the 2 attachments...

Have a good day,

Dave

 

[bmurch71]

No worries, Dave, I probably should have been more forthcoming about my limitations! Anyway, I feel comfortable playing around with stuff in Access in general, so if you will post the code you mentioned, I'll give it a shot. Thanks again for all the assistance.
Brian

 

[Davers]

No problem Brian...here is the code that saves my report as a .SNP file to a specific directory...

Private Sub Command29_Click()

Dim stDocName As String
Dim myDate As String
Dim myDir As String

myDate = Format(Me.Text0, "mm-dd-yy")
myDir = "W:\RCBWPA\Reports\DailyAuxReports\Year" & Format(myDate, "yyyy") & "\" & Format(myDate, "mmyyyy") & "\"
stDocName = "rptAUXCombined"

DoCmd.OutputTo acReport, stDocName, acFormatSNP, myDir & "DailyAuxReport-" & myDate & ".snp"

End Sub

Here is what the myDir string looks like:
W:\RCBWPA\Reports\DailyAuxReports\Year2004\122004

what its' doing is looking at a text box that houses yesterdays date...it saves the file as this:
DailyAuxReport-12-13-04.snp (always the previous days report)

Now this code:

Option Compare Database

Sub EmpInfo()

Dim myTo As String
Dim myCC As String
Dim mySub As String
Dim myAttach As String
Dim myAttach2 As String
Dim appOutlook As Object
Dim MailItem As Object

Set appOutlook = CreateObject("Outlook.Application")
Set MailItem = appOutlook.CreateItem(olMailItem)

myTo = "you@there.com"
myCC = "me@here.com"
mySub = "New Employee Information Request for... "
myAttach = "W:\RCBWPA\DaveDailyTasks\WPAStartPage.xls"
myAttach2 = "W:\rcbwpa\Reports\ForcedDisconnects\ForcedDisconnects-12-2004.xls"
myTime = Hour(Now())

If myTime < 12 Then
    mySalutation = "Good Morning,"
Else
    mySalutation = "Good Afternoon,"
End If

With MailItem
    .Subject = mySub
    .To = myTo
    .CC = myCC
    .Body = mySalutation & vbCrLf & vbCrLf & _
            "Please complete the attached Outlook Form and return" & vbCrLf & _
            "it as soon as possible for:" & vbCrLf & vbCrLf & _
             "Thank you and have a good day," & vbCrLf & vbCrLf & _
             "Workforce Planning and Administration" & vbCrLf & _
             "Percepta - Dearborn" & vbCrLf & "999-999-1234" & vbCrLf & "5S290"
    .BodyFormat = olFormatPlain
    .Attachments.Add myAttach
    .Attachments2.Add myAttach
    .Display
End With
    
    Set appOutlook = Nothing
    Set MailItem = Nothing
    
End Sub

is in module1....you will need to enable the Microsoft Outlook XX.X Object Library Reference for this code to work...I use this to e-mail a form letter along with 2 attachments....

Let me know if this helps or if you need a little more explanation.

Good luck and have a good day,

Dave

 

[bmurch71]

oi. you lost me. I guess I'm not as keen to "play around" with VB as I thought I was! (or as capable . . .) Some questions:

does the "Command29_Click()" part of your first batch of code refer to some control that I would have to create prior to using this code? I assume so, and I don't expect you to give me a VB tutoring session, but if you could let me know if I'm on the right track, that would be great. . .

would I replace the "DocName" part of "Dim stDocName As String" with the report file I want to save as a .snp?

Thanks for any help you have time to offer!
Sincerely,
Brian

 

[Davers]

Good Morning! No problem!! You are 100% correct for the Command29_Click(). That is the name Access gave to a button I created and put on the form....and yes again...you would replace this part:

"rptAUXCombined"

in this line:

stDocName = "rptAUXCombined"

with the name of your report...

Let me know if you are completely stuck Brian, and I'll help any way I can.

Have a good day and good luck!

Dave

 

[bmurch71]

Thanks for being willing to help, Dave. I need to back up a bit and sort of get my bearings: the way I was conceptualizing this was just to write a macro that would run the reports and email them out, and my users who need to do this would just double-click the macro to make that happen. I don't have any sort of application built to do these things, so, if you have a moment, can you sort of lay out your vision or way of doing things? That way, I can see what I might need to accomplish first - and really see where the gaps in my knowledge are. I am comfortable with queries, functions, and reports, but that's about where it ends (I'm taking a VB class next semester though, so I promise I won't be a burden forever!). Anyway, any structural ideas you could give me would be great, if you have time. Thanks again for all your help!
Brian