Email, VBA, dynamic cells and more

[allenmoe]

Maybe this information exists somewhere but I have searched and searched and come up with nothing that I can actually understand. Here are the basics:

1. I have a spread sheet containing customer information.
2. Among each customer's information is an email address in it's own cell.

I want to create a button for each customer that I can click on and have it generate an email in outlook with the email address, subject, and body filled in. This is not a problem. I have successfully created such a button. The problem is that I need to be able to sort the sheet as well as add/subtract lines and still have this button work properly. As it is now, if I sort the sheet or add a line all the buttons get off and send emails to the wrong email address. Can someone PLEASE tell me how to make this work? I know that the reference to the cell containing the email address needs to update automatically if the sheet is sorted or modified but I can't figure out how to make that happen. Here's my code for the button.

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 = Range("R41")
.Subject = "Organ Service Invoice"
.Body = "Hello" & vbNewLine & vbNewLine & "Thank you for your business! Attached you will find the invoice for the recently completed service call. Please let us know if you have any questions." & vbNewLine & "Sincerely," & vbNewLine & "Allen & Suzanne Moe" & vbNewLine & vbNewLine & "Moe Pipe Organ Company" & vbNewLine & "Wadena, Minnesota" & vbNewLine & "218-251-XXXX"
.Display ' Display the message in Outlook.
End With

' CLEAR.
Set objEmail = Nothing: Set objOutlook = Nothing

ErrHandler:
'

 

[sxhall]

Hi allenmoe,

I know of no easy way other than a heap more code to achieve the solutions with the buttons.

My suggestion would be to create a Userform with a button that would look like the below. This would have a dropdown list of the customer names and then you can click on the create e-mail button to run your code above. it would create an e-mail only for the customer selected with a bit of updating.

Not sure on your level for VBA but would be the below as quick guide...

1. Make sure your data is formatted as an Excel Table
2. Create the above Userform in VBA reference the data table for the dropdown combobox list
3. Add a button to your worksheet to display the above when required
4. Modify you code to reference the selected customer

By using the data in a table and then using a dropdown list you negate the issues you are currently having by assigning a customer to each button. You won't experience any issue with the sorting of the list as you currently are either. It will also grow as the list does, or inded shrink with it.

Steven