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