1

I am using an Excel file to send personalized emails from a shared Outlook inbox. The code for sending the email is working well, but I am missing how to save the sent email item (incl. it's attachments) to a local network location (let's call it "One Drive-User101").

Right now, I manually save each sent email as a PDF to the local folder and name it according to the recipient's personalized info (cell values).

This last bit of code would completely automate the task so I am desperate for the solution!

Here is the code I have now:

Sub send()
    
    Dim OutApp      As Object
    Dim OutMail     As Object
    Dim mailBody    As String
    Dim greet       As String
    Dim name        As String
    Dim x           As Integer
    Dim eRow        As Long
    
    eRow = Cells(Rows.Count, 15).End(xlUp).Row
    For x = 4 To eRow
        
        If Cells(x, 15) = "Ready" Then
            Set OutApp = CreateObject("Outlook.Application")
            Set OutMail = OutApp.CreateItem(0)
            
            mailBody = ActiveSheet.TextBoxes("confirm").Text
            greet = Cells(x, 33).Value
            name = Cells(x, 29).Value
            
            mailBody = Replace(mailBody, "Employee_Greeting", greet)
            mailBody = Replace(mailBody, "Employee_Last_Name", name)
            
            With OutMail
                .SentOnBehalfOfName = "oursharedinbox@company.com"
                .To = Cells(x, 27).Value
                .CC = Cells(x, 26).Value & ";" & Cells(x, 23).Value
                .Subject = "Confirmation"
                .HTMLBody = mailBody
                '.Attachments.Add ("C:\Users\OneDrive - User101\Confirmation Letter.pdf")
                .Display
                '.Send
                '.SaveAs
                '.PrintOut
            End With
            Set OutMail = Nothing
            
            Cells(x,15) = "Prepared"
        End If
        
    Next x
    
    Set OutApp= Nothing
    
End Sub
0m3r
  • 12,286
  • 15
  • 35
  • 71
  • Search the site. It is usual to SaveAs **.msg** but .pdf could work https://stackoverflow.com/questions/57379087/save-outlook-email-to-my-internal-drive-as-msg-file. You could manually select the applicable mail in the Sent Items folder then run SaveAs code. `ItemAdd` on the Sent Items folder would be appropriate to do this automatically if there is some criteria to look for. Try something. If not successful, edit the post so there is a question. – niton Jan 26 '21 at 22:19
  • https://stackoverflow.com/a/60555482/4539709 – 0m3r Jan 26 '21 at 23:23
  • vba example https://stackoverflow.com/a/32904076/4539709 – 0m3r Jan 26 '21 at 23:24

0 Answers0