Jyggalag
Active Member
- Joined
- Mar 8, 2021
- Messages
- 445
- Office Version
- 365
- 2019
- Platform
- Windows
Hi all,
I currently have this VBA code:
Right now, it takes an attachment stored in cell F2 and sends it out to the emails in the other cells.
However, I want my VBA code to:
1) Save the file as a PDF in this folder: R:\PRIVATE\COMPANY\DEPARTMENT\FOLDER 18\TEST\PDF files
2) Send this file out in the email just as before
3) Delete the PDF from the folder: R:\PRIVATE\COMPANY\DEPARTMENT\FOLDER 18\TEST\PDF files
Does anybody know if this is possible? And if so, can you help me change my VBA to make it work?
Any assistance at all would be immensely appreciated!
Thank you
Kind regards,
Jyggalag
I currently have this VBA code:
VBA Code:
Option Explicit
Private Const FilePath As String = "S:\COMPANY\FOLDER OF COMPANY\FOLDER 15\TEST FOLDER\Attachments\"
Sub send_email_complete()
Dim OutApp As Object
Dim OutMail As Object
Dim i As Long
Dim ws As Worksheet
Dim col As New Collection, itm As Variant
Dim ToAddress As String, CCAddress As String, EmailSubject As String
'~~> Change this to the relevant worksheet
'~~> that has the emails (right now Search Export has it)
Set ws = ThisWorkbook.Sheets("Search Export")
Set OutApp = CreateObject("Outlook.Application")
Dim BodyText As String
BodyText = ws.Range("G2") & "<BR>" & "<BR>" & _
"<b><u>" & ws.Range("G3") & "</b></u>" & " " & _
ws.Range("G4") & "<BR>" & "<BR>" & _
ws.Range("G5") & "<BR>" & _
ws.Range("G6")
Dim AttachmentName As String
AttachmentName = FilePath & ws.Cells(2, 6).Value2
For i = 2 To ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
'~~> Constructing addresses and subject
ToAddress = ws.Cells(i, 2).Value2 & ";" & ws.Cells(i, 3).Value2 & ";" & ws.Cells(i, 4).Value2
CCAddress = ws.Cells(i, 5).Value2
EmailSubject = ws.Cells(i, 1).Value2
'~~> This creates a new email (so we can send out multiple emails)
Set OutMail = OutApp.CreateItem(0)
With OutMail
.To = ToAddress
.CC = CCAddress
.Subject = EmailSubject
.HTMLBody = BodyText
.Attachments.Add AttachmentName
.Display
End With
Next i
End Sub
Right now, it takes an attachment stored in cell F2 and sends it out to the emails in the other cells.
However, I want my VBA code to:
1) Save the file as a PDF in this folder: R:\PRIVATE\COMPANY\DEPARTMENT\FOLDER 18\TEST\PDF files
2) Send this file out in the email just as before
3) Delete the PDF from the folder: R:\PRIVATE\COMPANY\DEPARTMENT\FOLDER 18\TEST\PDF files
Does anybody know if this is possible? And if so, can you help me change my VBA to make it work?
Any assistance at all would be immensely appreciated!
Thank you
Kind regards,
Jyggalag