Sending e-mail with attachment

[taccoo]

Hi All;

I would like to thank in advance for your time.

I'm having problems with the code for exporting one of my reports .csv format file and after that attaching it to an e-mail and send to one recipient via outlook, which is first time I'm trying to do and to be honest I can't say that I understood exactly how it works.

As result of the code, .csv file is generated and has been saved on desktop, outlook is also created the e-mail and sent it, but without attachment. I cannot figure it out what I'm missing.

Here is the code;

Private Sub CsvExp(Optional AttachmentPath)
 
Dim xlApp As Object
Dim EvName As String
Dim sFile As String
 
Set xlApp = CreateObject("Excel.Application")
Me!Event.SetFocus
EvName = Me!Event.Text
sFile = "C:\Users\User\Desktop\List For" & " " & EvName & ".csv"
 
    DoCmd.OutputTo acOutputReport, "CsvEx", acFormatXLS, sFile, True
 
    With xlApp
 
    .Application.DisplayAlerts = False
    .Application.ActiveWorkbook.SaveAs fileName:=sFile
    .Application.DisplayAlerts = True
    .Application.ActiveWorkbook.Save
    .Application.ActiveWorkbook.Close
    .Quit
 
    End With
 
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
 
   Set objOutlook = CreateObject("Outlook.Application")
   Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
 
   With objOutlookMsg
 
     Set objOutlookRecip = .Recipients.Add(recipient's mail address)
      objOutlookRecip.Type = olTo
 
      .Subject = sFile
 
      .Body = "Attached" & vbCrLf & vbCrLf
 
      If Not IsMissing(AttachmentPath) Then
 
         Set objOutlookAttach = .Attachments.Add(sFile)
 
      End If
 
      For Each objOutlookRecip In .Recipients
         objOutlookRecip.Resolve
         If Not objOutlookRecip.Resolve Then
         objOutlookMsg.Display
 
      End If
 
      Next
      .Send
 
   End With
 
   Set objOutlookMsg = Nothing
   Set objOutlook = Nothing
 
End Sub

Thanks a lot again.

taccoo

 

[taccoo]

I've resolved this issue with the help of HansV from Microsoft Communities. In case someone else will need it here is the solution;

--------------------------------------------------
Your procedure has an argument AttachmentPath but it isn't used when creating the .csv file. So you can omit it:
Change the procedure header to
Private Sub CsvExp()
and change
If Not IsMissing(AttachmentPath) Then
Set objOutlookAttach = .Attachments.Add(sFile)
End If
to
Set objOutlookAttach = .Attachments.Add(sFile)
-----------------------------------------------------

Thanks

taco