Using query to group attachments for email

[Monterey_Manzer]

Hello All,



I have been combing through the entries here trying to find something that will help me and I've seen a few things similar but nothing that answers all my questions.

The Situation:
I have a database that I use to enter and keep track of details regarding purchase requests. I use a query in this database as a data source for a mail merge in Word that generates my purchase request documents. Part of closing the loop on this process includes attaching these Word documents to the records in the Access data tables they correspond to. Now what I want to do is send these attachments via email to recipients based on other data field values. I have been looking into Automation since that seems to have been the answer for most people however all the Automation examples only show one attachment being added and that attachment is specified by a file path. I want to collect a group of attachments from the table in Access using an SQL statement with an WHERE clause. So my question is this:

Can I use Automation VBA script (http://support.microsoft.com/?kbid=161088) in addition to some data gathering technique like DAO Recordsets to collect the files I want to attach to the emails I am trying to send?

I am already pretty interested in the message modifications I can make using the features of the Outlook 14.0 Object library and I look forward to exploring it further but for now I'd just really like to be able to send purchase request documents to the right people using the data and attachments I have already put into my database.

Thanks!
Ryan

[rpeare]

so in essence you want to replace this:

' Add attachments to the message.

If Not IsMissing(AttachmentPath) Then
Set objOutlookAttach = .Attachments.Add(AttachmentPath)
End If

with code that cycles through a table that lists all of your attachments.

The short answer is yes, you can send multiple attachments per email with the method you've linked.

The question is, are you storing the final document name in your database or are you generating the file name by concantenating or manipulating data in a table?

As long as you can reliably pick the records you want as part of your email you can send all the attachments you want.

[Monterey_Manzer]

I am very happy to hear this is possible.

The attachments have already been generated as stand alone Word documents and then added to two separate fields per record with the data type "Attachment". How would I go about cycling through my table and adding those attachments? Can i use a Variant variable to hold all the files?

Thanks!
Ryan

[rpeare]

dim db as database
dim rst as recordset
dim sSQL as string
dim sattach1 as string
dim sattach2 as string

set db = currentdb
sSQL = "SELECT [FileField1], [FileField2] FROM tblTest WHERE (your where criteria here)"
set rst = db.openrecordset(sSQL)

'NOTE: if it is possible that you have a null dataset you'll have to test for that using if rst.recordcount = 0

rst.movefirst

do while rst.eof <> true
sattach1 = rst.fields("FileField1")
sattach2 = rst.fields("FileField2")
Set objOutlookAttach = .Attachments.Add(sAttach1)
Set objOutlookAttach = .Attachments.Add(sAttach2)
rst.movenext
loop
rst.close
set db = nothing

I haven't tested this code specifically but you get the idea.

[Monterey_Manzer]

I ran the above code and I get a type mismatch between the sAttach1 and the File Field. To be clear, this field is an actual attachment and not simply the file path for the attachment. If it would make everything simpler to just make "FileField1" and "FileField2" text fields with the file path for the actual Word documents rather than attachment fields with copies of the Word documents, let me know and I can pursue that route.

[rpeare]

to attach a document you have to give it the fulll path/file name or the code won't know what file to send. Post the code you're using for the email portion.

[Monterey_Manzer]

Alright, I revised my code to generate the full file path rather than simply referring to the file in the attachment. To be clear, I was loading each document into the Access database into attachment fields for each record. I then attempted to use a DAO Recordset to gather the actual files and attach them that way. Now I use some module level variables to set the file path string for each order I am looking for, however I am still running into some problems here.
The code is below:

Dim objOutlook As Outlook.Application
          Dim objOutlookMsg As Outlook.MailItem
          Dim objOutlookRecip As Outlook.Recipient
          Dim objOutlookAttach As Outlook.Attachment
          Dim db As DAO.Database, rst As DAO.Recordset, strSQL As String
          

          ' Create the Outlook session.
          Set objOutlook = CreateObject("Outlook.Application")

          ' Create the message.
          Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
            ' Setting values to the below variables using the code in the subroutine EmailN
            Call EmailN

          With objOutlookMsg
              ' Send to the correct person
              Set objOutlookRecip = .Recipients.Add("strRecip")
              objOutlookRecip.Type = olTo

              ' Add the CC recipient(s) to the message.
              Set objOutlookRecip = .Recipients.Add(strCC)
              objOutlookRecip.Type = olCC

             ' Set the Subject, Body, and Importance of the message.
             .Subject = strSubject
             .Body = strMsg
             .Importance = olImportanceHigh  'High importance

             ' Add attachments to the message.
             Set db = CurrentDb
    
             strSQL = "SELECT [MIPRs by Participant].[Fiscal Year], PartInfo.[Last Name], PartInfo.[Sponsoring Service]" & _
             " FROM PartInfo INNER JOIN [MIPRs by Participant] ON PartInfo.[Smart Id] = [MIPRs by Participant].[SMART ID]" & _
             " WHERE ((([MIPRs by Participant].[Fiscal Year])='" & FYear & "') AND ((PartInfo.[Sponsoring Service])='" & Service & "'));"

    
            Set rst = db.OpenRecordset(strSQL)
            Do While Not rst.EOF
                sAttach1 = "\\comfort\SMART$\SMART\MIPRs\MIPR Memos\" & FYear & "\" & rst![Last Name] & " MIPR Memo Oct 2013.docx"
                sAttach2 = "\\comfort\SMART$\SMART\MIPRs\SOWs\" & FYear & "\" & rst![Last Name] & " SOW Oct 2013.docx"
                Set objOutlookAttach = .Attachments.Add(sAttach1)
                Set objOutlookAttach = .Attachments.Add(sAttach2)
                rst.MoveNext
            Loop
            rst.Close
            Set db = Nothing
            
             ' Resolve each Recipient's name.
             For Each objOutlookRecip In .Recipients
                 objOutlookRecip.Resolve
             Next
            
            .Display
             
          End With
          Set objOutlook = Nothing


        
    
End Sub

Sub EmailN()
    Dim db As DAO.Database, rstService As DAO.Recordset, rstAttach As DAO.Recordset, strLiaison As String
    Dim strAttach As String, strPartList As String, sAttach1 As String, sAttach2 As String
    
    Set db = CurrentDb
    ' Getting the liaison Email to make sure they are CC'd in the email to Nancy
    strLiaison = "SELECT Email FROM ServiceLiasonInfo WHERE Service = '" & Service & "';"
    Set rstService = db.OpenRecordset(strLiaison)
    strCC = rstService![Email] & "; jlsimons@nps.edu" ' CC'ing liaison and Janna
    strRecip = "nancy.seeger.ctr@osd.mil"
    
    ' Getting the Attachments for the emails to Nancy
    strAttach = "SELECT PartInfo.[Sponsoring Service], [MIPRs by Participant].MIPRMemo," & _
    " [MIPRs by Participant].SOW, [MIPRs by Participant].[Fiscal Year], PartInfo.[Participant Name]" & _
    " FROM PartInfo INNER JOIN [MIPRs by Participant] ON PartInfo.[Smart Id] = [MIPRs by Participant].[SMART ID]" & _
    " WHERE (((PartInfo.[Sponsoring Service])='" & Service & "')" & _
    " AND (([MIPRs by Participant].[Fiscal Year])='" & FYear & "'));"
    Set rstAttach = db.OpenRecordset(strAttach)
        Do While Not rstAttach.EOF
            strPartList = strPartList & vbCr & rstAttach![Participant Name]
            rstAttach.MoveNext
        Loop
            
    ' Writing the email - getting the subject line
    strSubject = Service & " MIPR Initiating Documents for " & FYear
    ' Writing the email - getting the body message
    strMsg = "Nancy," & vbCr & vbCr & "Attached are the " & FYear & " MIPR Documents for all " & _
    Service & " participants.  Below is a list of the " & Service & " RT participants.  If any participants" & _
    " are listed below but do not have documents attached, please let me know." & vbCr & vbCr & _
    Service & " Participants:" & vbCr & strPartList & vbCr & vbCr & _
    "Please contact me if you have any questions or concerns regarding these documents or anything MIPR related" & _
    vbCr & vbCr & strSig
    

    rstService.Close
    Set rstService = Nothing
    rstAttach.Close
    Set rstAttach = Nothing
    db.Close
    Set db = Nothing

[rpeare]

What's the error? If you are actually storing the document (which I wouldn't recommend) what happens if you use a debug statement to find out the value of last name

type debug.print rst![Last Name] and see if it's actually returning a value.

If it is try

sAttach1 = "\\comfort\SMART$\SMART\MIPRs\MIPR Memos\" & FYear & "\" & cstr(rst![Last Name]) & " MIPR Memo Oct 2013.docx"

If you're storing it as an object rather than a text field you may need to perform the cstr operation to make sure it's interpreting it as a string.

Just one other word on storing the object in your database, it's going to bloat the size of your database fairly quickly and access starts to break down once it reaches about 2gb (or used to) in size so just be aware of the issue and compact/repair on a regular basis.