Gingertrees
Well-known Member
- Joined
- Sep 21, 2009
- Messages
- 697
I created an email button on my excel workbook that I distribute to my colleagues to send in to me. They fill out one of these forms for each customer they sign up for a new program; among the info gathered is our internal customer ID, business name, and contact info. The Customer ID and business name are both defined names in my workbook and appear on the same sheet as the email button.
What I'd like to do:
have the subject of this email include the customer ID and business name, plus some standard text.
So if the Customer ID is AB123, and the business name is Widgetco, I want my email to have the subject
"AB123 Widgetco program enrollment"
NOTE: If someone can only figure out the Windows side of this question, that's totally cool! I don't work with too many Macs. Don't let that stop you from responding if you can answer half my question. Thanks!
Here's my email button code (thanks to Ron DeBruin's helpful site for this) :
What I'd like to do:
have the subject of this email include the customer ID and business name, plus some standard text.
So if the Customer ID is AB123, and the business name is Widgetco, I want my email to have the subject
"AB123 Widgetco program enrollment"
NOTE: If someone can only figure out the Windows side of this question, that's totally cool! I don't work with too many Macs. Don't let that stop you from responding if you can answer half my question. Thanks!
Here's my email button code (thanks to Ron DeBruin's helpful site for this) :
Code:
Private Sub CommandButton3_Click() 'email button
'Should work in 97-2010
'Test the OperatingSystem
ActiveWorkbook.Save
If Not Application.OperatingSystem Like "*Mac*" Then
'I am Windows
Call WindowsEmailButton
Else
'I am a Mac and will test if it is Excel 2011 or higher
If Val(Application.Version) > 14 Then
Call MacEmailButton
End If
End If
End Sub
Sub WindowsEmailButton()
'!!!!must have a desktop email application (Outlook, LocustNotes, etc)!!!!
Dim wb As Workbook
Dim I As Long
Set wb = ActiveWorkbook
If Val(Application.Version) >= 12 Then 'Excel 2007 or later
'Excel 2007 = 12, 2010 = 14, Mac 2011 = 14.1 or 14,1, pre-2007 = 8-11
If wb.FileFormat = 51 And wb.HasVBProject = True Then
MsgBox "There is VBA code in this xlsx file, there will" & vbNewLine & _
"be no VBA code in the file you send. Save the" & vbNewLine & _
"file first as xlsm and then try the macro again.", vbInformation
Exit Sub
End If
End If
On Error Resume Next
For I = 1 To 3
wb.SendMail "me@email.com, jeff@email.com", _
"Program Enrollment"
If Err.Number = 0 Then Exit For
Next I
On Error GoTo 0
End Sub
Sub MacEmailButton()
'!!!!must have Outlook for Mac!!!!
'For Excel 2011 for the Mac and Outlook 2011
'Note: The workbook must be Saved ones
Dim wb As Workbook
If Val(Application.Version) < 14 Then Exit Sub
Set wb = ActiveWorkbook
With wb
MailFromMacwithOutlook bodycontent:="Program enrollment", _
mailsubject:="Program Enrollment signup mac", _
toaddress:="me@email.com, jeff@email.com", _
ccaddress:="", _
bccaddress:="", _
attachment:=.FullName, _
displaymail:=False
End With
Set wb = Nothing
End Sub