I have already created a workbook that will populate the current employee’s emails on worksheet 1 in cell A1.
The email message (may change) that I would like to email to the employee’s is located on worksheet 1 in cell A3.
Also on some occasions I may also include an attachment (most likely a PDF)
I also have set a reference to Microsoft CDO 1.21 Library, via Tools > References in the VBA editor.
Here is some code I previously using for Outlook if that helps
Code: Select all
Private Sub CommandButton1_Click()
Dim OutApp As Object
Dim OutMail As Object
Dim i As Long
Dim fd As FileDialog
Dim strbody As String
Shell "Outlook.exe", vbNormalFocus
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
strbody = ("Hi ") & Sheets("Sheet1").Range("A1") & vbNewLine 'emails addresses
On Error Resume Next
With OutMail
.To = Sheets("Sheet1").Range("A1") 'email addresses
.BCC = ""
.Subject = Sheets("sheet1").Range("D3") 'email message
.Body = strbody
'Attach documents here
Set fd = Application.FileDialog(msoFileDialogOpen)
fd.AllowMultiSelect = True
If fd.Show Then
For i = 1 To fd.SelectedItems.Count
.Attachments.Add fd.SelectedItems(i)
Next i
End If
Set fd = Nothing
.Display 'or use .Display or .Send
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
