Attach multiple files into email from Access 2

[Eprice]

I have an Access XP form that makes multiple pdf reports and saves them to a folder depending on what the log number is. All of the code works as it should to create the reports and save them but I need to attach them to an email afterwards. I don't know how to write the code to attach every file in the folder if I don't know how many there will be or what the file names will be each time. This is the code that I am trying to use and I get an error trying to use and asterick.

Dim rs As Object, rsLogNo As String

' I use this table to get the Log Number to use in the loop which is the name of the folder the code creates in N:\LCM
Set rs = CurrentDb().OpenRecordset("tblLogNumbers")

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

rs.movefirst
Do While Not rs.EOF
rsLogNo = rs.LogNo
With MailOutLook
.To = "lprice@somewhere.com"
.CC = ""
.BCC = ""
.Subject = rsLogNo
.Body = "Test"
.Attachments.Add "N:\LCM\" & rsLogNo & "*.pdf"
.Send
End With
rs.MoveNext
Loop
Set rs = Nothing
If you need the complete code I use for creating the folders and pdf reports I will post although it is long.
Thanks
Lisa

 

[CautionMP]

Eprice,
Would something like this work?

...
        .Body = "Test"
        strFile = Dir("N:\LCM\" & rsLogNo & "\*.pdf")
        Do
            .Attachments.Add "N:\LCM\" & rsLogNo & "\" & strFile
            strFile = Dir
        Loop until strFile = ""
        .Send
...

CMP

(GMT-07:00) Mountain Time (US & Canada)

 

[Eprice]

I have it partly working by using this:

'Open outlook and email all attachments in each seperate folder on N:LCM
Dim rs As Object, rsLogNo As String
Set rs = CurrentDb().OpenRecordset("tblInsurityLogNumbers")
Dim appOutlook As Outlook.Application
Dim MailOutLook As Outlook.MailItem
Set appOutlook = CreateObject("Outlook.Application")
Set MailOutLook = appOutlook.CreateItem(olMailItem)

'I can't get this to work either!!
'Dim OlSecurityManager As Outlook.SecurityManager
'OlSecurityManager.ConnectTo (appOutlook)
'OlSecurityManager.DisableOOMWarnings = True

rs.movefirst
Do While Not rs.EOF
rsLogNo = rs.InsurityLogNo

With MailOutLook
.To = "lprice@somewhere.com"
.CC = ""
.BCC = ""
.Subject = rsLogNo
.Body = "Test LCM Transfer"
Dim strFile As String
strFile = Dir("N:\LCM\" & rsLogNo & "\*.pdf")

Do While strFile <> ""
If strFile <> "." And strFile <> ".." Then
If (GetAttr("N:\LCM\" & rsLogNo & "\" & strFile) And vbDirectory) <> vbDirectory Then
.Attachments.Add "N:\LCM\" & rsLogNo & "\" & strFile
End If
End If
strFile = Dir
Loop
.Send
End With

rs.MoveNext
Loop

Set appOutlook = Nothing
Set MailOutLook = Nothing
Set rs = Nothing
'OlSecurityManager.DisableOOMWarnings = False

The problem is I am getting the security warning and don't know how to shut it off AND after I click ok on the security warning it only sends one email instead of one email for each folder and bombs on the email address line. It says the item has been removed or deleted. any ideas?
Lisa

 

[CautionMP]

I am getting the security warning and don't know how to shut it off

Got nothing for you here, rumor has it that Collaboration Objects will get around this.

after I click ok on the security warning it only sends one email instead of one email for each folder

Open outlook and email all attachments in each seperate folder on N:LCM
Dim rs As Object, rsLogNo As String
Set rs = CurrentDb().OpenRecordset("tblInsurityLogNumbers")
Dim appOutlook As Outlook.Application
Dim MailOutLook As Outlook.MailItem
Set appOutlook = CreateObject("Outlook.Application")
'[s]Set MailOutLook = appOutlook.CreateItem(olMailItem)[/s]
...
rs.movefirst
Do While Not rs.EOF
    rsLogNo = rs.InsurityLogNo
    Set MailOutLook = appOutlook.CreateItem(olMailItem)
...

I can't remeberm but you may need to set [tt]MailOutLook = Nothing[/tt] at the bottom of the loop.

bombs on the email address line.

...
    With MailOutLook
        '[s].To = "lprice@somewhere.com"[/s]
        .Recipients.Add ("lprice@somewhere.com")
        .CC = ""
...

Hope this helps,
CMP

(GMT-07:00) Mountain Time (US & Canada)

 

[Eprice]

Perfect! It goes to each folder and sends an email with everything attached now. I only have the problem of having to hit okay for each attachement for outlook security manager now. If you think of anything please let me know.
Thanks again
Lisa

 

[PHV]

Do a google search for outlook object model guard

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886