Including an attachment in automated email

[betheball]

I am using the following code to generate a weekly email message. Is there some code I can include to have it pick up an attachment to include in the message? If so, what is the proper syntax?

Sub Item_PropertyChange(ByVal Name)
Select Case Name
Case "Status"
if Item.Status = 2 then '2 = Completed
Set NewItem = Application.CreateItem(0)
NewItem.To = "test2@usa.gov"
NewItem.CC = "test@usa.gov NewItem.Recipients.ResolveAll
NewItem.Subject = "Team Meeting - Ops. 2 Dept. 3 Team 304"
NewItem.Body = "Attached is the agenda for Team 304's upcoming Team Meeting (next Monday)."
NewItem.Display
End IF
End Select
End Sub

 

[jaydwest]

If you are using the DoCmd.SendObject command to send your email, it is my understanding you cannot attach emails. There are some folks at this site who have used other commands to send email wil attachments. Hopefully one of them will respond.

Good Luck.

 

[Fizzio]

I use the outlook object model to do this so am not familiar with the Application.CreateItem(0) but maybe you could try

NewItem.Attachment = "C:\MyDocuments\MyFile" 'if it is a stored file

 

[betheball]

I am open to other ways to accomplish this task. What I need is to send basically the same email with the same attachment every Monday morning. I followed the instructions in this MS article: http://support.microsoft.com/default.aspx?scid=kb;en-us;239087

However, I can't seem to get it to include my attachment. I tried NewItem.Attachment but got "Object doesn't support this property or method:'Attachment' I also tries AttachFile and a couple of others all with the same result.

 

[Fizzio]

The code you are using is part of the outlook object (duh) but I use the name ie olMailItem rather than 0

the correct syntax for adding an attachment is

NewItem.Attachments.Add Source:= "File Location"

If no joy, I'll post the code I use to create an e-mail.

 

[betheball]

I ran the code as follows:

Sub Item_PropertyChange(ByVal Name)
Select Case Name
Case "Status"
if Item.Status = 2 then '2 = Completed
Set NewItem = Application.CreateItem(0)
NewItem.To = "duane.d.thomas@usa.gov"
NewItem.CC = "todd.m.thomas@usa.gov"
NewItem.Recipients.ResolveAll
NewItem.Subject = "Team Meeting - Ops. 2 Dept. 3 Team 304"
NewItem.Body = "Attached is the agenda for Team 304's Team Meeting to be held next Monday."
NewItem.Attachments.Add Source = "C:\Team405MeetingAgenda.doc"
NewItem.Display
End IF
End Select
End Sub

I get an error message saying, "One or more parameter values are not valid" The reference is to line 11 which is the line for the attachment.

 

[Fizzio]

Here is the code I use to send an e-mail

Sub SendEMail(strRecipient As String, strSubject As String, strBody As String, strAttachments As String)

Dim bStarted As Boolean
Dim oOutlookApp As Outlook.Application
Dim oItem As Outlook.MailItem
Dim intAtt As Integer
On Error Resume Next

Set oOutlookApp = GetObject(, "Outlook.Application")
If Err <> 0 Then
    Set oOutlookApp = CreateObject("Outlook.Application")
    bStarted = True
End If

Set oItem = oOutlookApp.CreateItem(olMailItem)

With oItem
    .To = strRecipient
    .Subject = strSubject
    .body = strBody
    .Attachments.Add Source:="File to Send" Type:=olByValue, _
            DisplayName:="File Description"
     .Send
End With

If bStarted Then
    oOutlookApp.Quit
End If

Set oItem = Nothing
Set oOutlookApp = Nothing

End Sub

 

[betheball]

Hey Fizzio, thanks for sticking with me here. When I tried the code you provided I got an "expected )" error in line 1. I don't understand that as both the opening and closing parens are there. Any ideas?

 

[Fizzio]

I'm sure it is because all the variables are required - all the emails I send have attachments and so the strAttachments is required - I actually have not posted the exact code I use because I send a list of attachments and it splits them up. This modification should be ok

Sub SendEMail(strRecipient As String, strSubject As String, strBody As String, optional strAttachment As String)

Dim bStarted As Boolean
Dim oOutlookApp As Outlook.Application
Dim oItem As Outlook.MailItem
Dim intAtt As Integer
On Error Resume Next

Set oOutlookApp = GetObject(, "Outlook.Application")
If Err <> 0 Then
    Set oOutlookApp = CreateObject("Outlook.Application")
    bStarted = True
End If

Set oItem = oOutlookApp.CreateItem(olMailItem)

With oItem
    .To = strRecipient
    .Subject = strSubject
    .body = strBody
if strAttachments then
    .Attachments.Add Source:=strAttachment Type:=olByValue, _
            DisplayName:="Attachment"
end if
     .Send
End With

If bStarted Then
    oOutlookApp.Quit
End If

Set oItem = Nothing
Set oOutlookApp = Nothing

End Sub

Get back with any problems

 

[betheball]

I know very little about VB, so I am not making much progress. I am unsure as to where I put the data I need for the email such as the to address, the subject, etc. Do I insert it in the section that begins with "With oItem" or do I insert that info in line 1? Can you post an example complete with sample email address, subject, body and attachment? I apologize for being slow in figuring this one out. With the last code you gave me, I modified it to exactly what is shown below and still got an error saying expected ')' on line 1. Please check and see if I have forgotten or missed completing something.

Sub SendEMail(strRecipient As String, strSubject As String, strBody As String, optional strAttachment As String)

Dim bStarted As Boolean
Dim oOutlookApp As Outlook.Application
Dim oItem As Outlook.MailItem
Dim intAtt As Integer
On Error Resume Next

Set oOutlookApp = GetObject(, "Outlook.Application")
If Err <> 0 Then
Set oOutlookApp = CreateObject("Outlook.Application")
bStarted = True
End If

Set oItem = oOutlookApp.CreateItem(olMailItem)

With oItem
.To = "duane.d.thomas@usa.gov"
.Subject = "Test"
.body = "This is a test"
if strAttachments then
.Attachments.Add Source:= "C:\Agenda.doc Type:=olByValue, _
DisplayName:="Attachment"
end if
.Send
End With

If bStarted Then
oOutlookApp.Quit
End If

Set oItem = Nothing
Set oOutlookApp = Nothing

End Sub

 

[betheball]

Unbelievable how close I was from the very beginning. The following code works. The only change I needed was to delete the "=" sign on the line that inserts the attachment. Seemed to me that if every other variable was NewItem.variable = then so would the attachement line. Anyway, hope this helps someone else someday and thanks to all those that helped.

Sub Item_PropertyChange(ByVal Name)
Select Case Name
Case "Status"
if Item.Status = 2 then '2 = Completed
Set NewItem = Application.CreateItem(0)
NewItem.To = "Test@anywhere.com"
NewItem.CC = "test2@anywhere.com"
NewItem.Recipients.ResolveAll
NewItem.Subject = "Team Meeting - Ops. 2 Dept. 3 Team 304"
NewItem.Body = "Attached is the agenda for Team 304's Team Meeting to be held next Monday."
NewItem.Attachments.Add "C:\Agenda.doc"
NewItem.Display
End IF
End Select
End Sub

 

[Fizzio]

Glad you sorted it. I was soooo close as well