A | B | C | D | E | F | |
Row 1 | To: | cc | Subject | Body Text | Path of Attachment folder | Attachment name |
Row 2 | abc@email1.com | abc@email1.com | test email 1 | For your information... | C:\Users\ABC\Desktop\SavedFolder\Folder1 | 12192022Invoice12.pdf |
Hi,
I want to display emails with an attachment from a certain folder. It will be used by multiple users so the path is different and the users must enter their path in row 2, column E.
In the folder, there will be multiple documents with all different name. If users enter the file name in row 2, column F, VBA create an email with the attachment.
Below is the code I currently use.
Code:
Private Sub CommandButton1_Click()
Dim strbody As String
Dim xStrFile As String
Dim xFilePath As String
Dim xFileDlg As FileDialog
Dim xFileDlgItem As Variant
Dim xOutApp As Outlook.Application
Dim xMailOut As Outlook.MailItem
Application.ScreenUpdating = False
Set xOutApp = CreateObject("Outlook.Application")
Set xMailOut = xOutApp.CreateItem(olMailItem)
Set xFileDlg = Application.FileDialog(msoFileDialogFilePicker)
If xFileDlg.Show = -1 Then
With xMailOut
.To = ""
.CC = Rows(2).Columns(4).Value
.BCC = Rows(2).Columns(3).Value
.Subject = Rows(2).Columns(5).Value
.Body = Rows(2).Columns(6).Value
For Each xFileDlgItem In xFileDlg.SelectedItems
.Attachments.Add xFileDlgItem
Next xFileDlgItem
.Display
End With
End If
Set xMailOut = Nothing
Set xOutApp = Nothing
Application.ScreenUpdating = True
End Sub