OTWarrior
01-31-2012, 07:12 AM
Hello
I have been asked to create a button in excel, which will generate an email using the data held on a spreadsheet.
Based upon the contents of column B (which is a dropdown) all rows with column B matching the currently selected row, need to be put into a single table within an email. Only certain fields from each row will be needed.
Now, I know how to generate the email based on the current row (and how to put that rows data within the email) but I am unsure how to do this for more than one row at once.
The user is happy to use autofilter to narrow down the to the set criteria. If this is ok, how can I copy the entire visible spreadsheet (only the visible filtered data) into an email as a table.
The code I have thus far is as follows:
Public Sub TipsEmail()
Set olApp = CreateObject("Outlook.Application")
Set objMail = olApp.CreateItem(0)
Set objMail = olApp.CreateItem(olMailItem)
Dim tipsSubject As String
Dim tipsBodyText
Dim CurrentCellRow As Long
CurrentCellRow = ActiveCell.Row
tipsSubject = "Tips #" & Range("A" & CurrentCellRow).Text _
& " - " & Range("B" & CurrentCellRow).Text
objMail.Subject = tipsSubject
tipsBodyText = Range("C" & CurrentCellRow).Text
objMail.body = tipsBodyText
objMail.Display
End sub
Thank you
I have been asked to create a button in excel, which will generate an email using the data held on a spreadsheet.
Based upon the contents of column B (which is a dropdown) all rows with column B matching the currently selected row, need to be put into a single table within an email. Only certain fields from each row will be needed.
Now, I know how to generate the email based on the current row (and how to put that rows data within the email) but I am unsure how to do this for more than one row at once.
The user is happy to use autofilter to narrow down the to the set criteria. If this is ok, how can I copy the entire visible spreadsheet (only the visible filtered data) into an email as a table.
The code I have thus far is as follows:
Public Sub TipsEmail()
Set olApp = CreateObject("Outlook.Application")
Set objMail = olApp.CreateItem(0)
Set objMail = olApp.CreateItem(olMailItem)
Dim tipsSubject As String
Dim tipsBodyText
Dim CurrentCellRow As Long
CurrentCellRow = ActiveCell.Row
tipsSubject = "Tips #" & Range("A" & CurrentCellRow).Text _
& " - " & Range("B" & CurrentCellRow).Text
objMail.Subject = tipsSubject
tipsBodyText = Range("C" & CurrentCellRow).Text
objMail.body = tipsBodyText
objMail.Display
End sub
Thank you