Email, VBA, dynamic cells and more

allenmoe

New Member
Joined
May 23, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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:
'
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

sxhall

Board Regular
Joined
Jan 5, 2005
Messages
150
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.

1623033347579.png


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
 

Forum statistics

Threads
1,144,515
Messages
5,724,814
Members
422,579
Latest member
parsnipsnatcher

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top