Outlook Attachments via Recordset

[lgray]

Can someone take a look through this code to see if they can find the error
in the code. The code is supposed to cycle through a recordset of
path/files located in a table and attach them into an email.

The email part works, the attachment part works if I hard code the path into
VBA. But when I try to attach the file via a recordset I get an error that
reads. "Cant create file:" It then provides the correct name of the file,
and then reads, "Right-click on the folder you want to create the file in,
and then click Properties on the shortcut menu to check your permissions for
the folder.

On debug, the following message of the code is highlighted:
newMailAttachments.Add File1, olByValue, 1, "Test Doc"


Sub createOutlookMailItem()

Dim EDB_V052708_WIP As Database
Dim rs As Recordset
Dim objOutlook As Outlook.Application
Dim newMail As MailItem

Set objOutlook = CreateObject("Outlook.application")
Set newMail = objOutlook.CreateItem(olMailItem)
Set EDB_V052708_WIP = CurrentDb


Set newMailAttachments = newMail.Attachments
Set rs = EDB_V052708_WIP.OpenRecordset("Select * from
Encroachment_Attachments")
File1 = rs("FullPathToFile")
newMail.To = "(e-mail address removed)"
newMail.Subject = "Encroachment Documents"

While Not rs.EOF
newMailAttachments.Add File1, olByValue, 1, "Test Doc"
Wend

newMail.Display
newMail.Subject = "Documents Confirmation"
newMail.OriginatorDeliveryReportRequested = True
newMail.ReadReceiptRequested = True
newMail.Display

Set newMailAttachments = Nothing
Set newMail = Nothing
Set objOutlook = Nothing


End Sub

 

[roger]

When it errors, and highlights that line go to the immediate window and type:

?file1

if your code up that point is correct it should with a valid file path.

If it does then the error is in the newMailAttachments.Add line

if not then its somewhere before that, like the set rs= line or like that

hth roger

 

[pietlinden]

Can someone take a look through this code to see if they can find the error
in the code.  The code is supposed to cycle through a  recordset of
path/files located in a table and attach them into an email.

The email part works, the attachment part works if I hard code the path into
VBA.  But when I try to attach the file via a recordset I get an error that
reads.  "Cant create file:"  It then provides the correct name of thefile,
and then reads, "Right-click on the folder you want to create the file in,
and then click Properties on the shortcut menu to check your permissions for
the folder.

On debug, the following message of the code is highlighted:
       newMailAttachments.Add File1, olByValue, 1, "Test Doc"

Sub createOutlookMailItem()

    Dim EDB_V052708_WIP As Database
    Dim rs As Recordset
    Dim objOutlook As Outlook.Application
    Dim newMail As MailItem

    Set objOutlook = CreateObject("Outlook.application")
    Set newMail = objOutlook.CreateItem(olMailItem)
    Set EDB_V052708_WIP = CurrentDb

    Set newMailAttachments = newMail.Attachments
    Set rs = EDB_V052708_WIP.OpenRecordset("Select * from
Encroachment_Attachments")
    File1 = rs("FullPathToFile")
    newMail.To = "(e-mail address removed)"
    newMail.Subject = "Encroachment Documents"

    While Not rs.EOF
        newMailAttachments.Add File1, olByValue, 1, "Test Doc"
        Wend

    newMail.Display
    newMail.Subject = "Documents Confirmation"
    newMail.OriginatorDeliveryReportRequested = True
    newMail.ReadReceiptRequested = True
    newMail.Display

    Set newMailAttachments = Nothing
    Set newMail = Nothing
    Set objOutlook = Nothing

End Sub

Looks like this is the problem:

While Not rs.EOF
newMailAttachments.Add File1, olByValue, 1, "Test Doc"
Wend

The extra step of setting File1=rs("FullPathToFile") *outside* of the
loop means that your code never reads the next file name (in the next
record). So instead of using File1 at all, just put the whole thing
inside the loop

 

[lgray]

I continued to be humbled by this seemingly simple code.....Below is my
version of the code. I get the following error when I run it.
"Run-time error - 1836446541 (928a0cb3):
Can't find this file. Make sure path and filename are correct:
On debug it highlights this statement - "newMail.Attachments.Add
("Encroachment_Attachments")"

Sub createOutlookMailItem()

THE CODE:
Dim EmailCode As Database
Dim rs As Recordset
Dim objOutlook As Outlook.Application
Dim newMail As MailItem

Set objOutlook = CreateObject("Outlook.application")
Set newMail = objOutlook.CreateItem(olMailItem)

Set EmailCode = CurrentDb
newMail.Attachments.Add ("Encroachment_Attachments")

newMail.To = "(e-mail address removed)"
newMail.Subject = "Encroachment Documents"

Set rs = EmailCode.OpenRecordset("Encroachment_Attachments")
While Not rs.EOF
newMail.Attachments.Add rs.Fields("FullPathToFile"), olByValue, 1, "Test Doc"
Wend

newMail.Display
Set newMailAttachments = Nothing
Set newMail = Nothing
Set objOutlook = Nothing
End Sub