I have this VBA i've been using with command buttons in a spreadsheet to send emails to doctors but the list is getting larger and I think a drop down list would be much more beneficial to select a doctors name than have 40 buttons. Is it possible to adjust this to make it reference cell H1 in this sample workbook where it has .To = ""
Private Sub CommandButton1_Click()
On Error GoTo ErrHandler
' SET Outlook APPLICATION OBJECT.
Dim objOutlook As Object
Set objOutlook = CreateObject("Outlook.Application")
' CREATE EMAIL OBJECT.
Dim objEmail As Object
Set objEmail = objOutlook.CreateItem(olMailItem)
With objEmail
.To = ""
.Subject = Format(Date - Day(Date), "mmm yyyy") & " " & "-" & " " & "EOM Reports"
.HTMLBody = "<BODY style=font-size:11pt;font-family:Calibri>" & "<br>" & "<b>Attached is your monthly productivity, collections summary and appointments dashboard:" & "<br>" & "<br>" & "<br>" & "Thank you,"
.Display ' Display the message in Outlook.
End With
' CLEAR.
Set objEmail = Nothing: Set objOutlook = Nothing
ErrHandler:
'
End Sub
eileens.xlsx
You do not have the required permissions to view the files attached to this post.
Private Sub CommandButton1_Click()
If Range("H1").Value = "" Then
Beep
Exit Sub
End If
On Error GoTo ErrHandler
' SET Outlook APPLICATION OBJECT.
Dim objOutlook As Object
Set objOutlook = CreateObject("Outlook.Application")
' CREATE EMAIL OBJECT.
Dim objEmail As Object
Set objEmail = objOutlook.CreateItem(0)
With objEmail
.To = Range("H1").Value
.Subject = Format(Date - Day(Date), "mmm yyyy") & " " & "-" & " " & "EOM Reports"
.HTMLBody = "<BODY style=font-size:11pt;font-family:Calibri>" & "<br>" & _
"<b>Attached is your monthly productivity, collections summary and appointments dashboard:" & _
"<br>" & "<br>" & "<br>" & "Thank you,"
.Display ' Display the message in Outlook.
End With
' CLEAR.
Set objEmail = Nothing
Set objOutlook = Nothing
ErrHandler:
End Sub
