L
Legacy 286866
Guest
I have an excel spreadsheet that select pre-defined cells and from this creates and email when a user presses a button. This worked fine when I had about 3 to 4 rows of data but now I have over 500 rows.
Please bare with me here as my coding in VBA is not so good. What I would like to do is instead of duplicating the code for each row is have one function that gets called on each time. I want the code to work out the row from a link at the end of the Row (which I also need to figure out how to link to the VBA, I know how to do it via a button but a link at the end of each row would be much better). The Link will say send email. If the user presses this link, then it will select the row the link is on and send the email. Hope that makes sense. I just wanted 1 function this could be called from. Instead of having to duplicate the code each time for each row. Any ideas?
Any good ways of doing this? Please see my code and spreadsheet below.
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;">Sub SendEmail()
Dim objOutlook As Outlook.Application
Set objOutlook =New Outlook.Application
Dim objEmail As Outlook.MailItem
Set objEmail = objOutlook.CreateItem(olMailItem)
objEmail.Subject = Cells(2,1).Text
objEmail.Body ="============"& vbNewLine & Cells(2,3).Text & vbNewLine &"============"& vbNewLine & Cells(2,6).Text
objEmail.To= Cells(2,5).Text
objEmail.SentOnBehalfOfName ="test@test.com"
objEmail.Display
EndSub
</code>I have also attached an example of my spreadsheet. Please note the full on spreadsheet has over 500 records. This is a much condensed version:
https://drive.google.com/folderview...dnekVMREZmVWpPT3h6RmxHRGFRQ3RHYTQ&usp=sharing
Please bare with me here as my coding in VBA is not so good. What I would like to do is instead of duplicating the code for each row is have one function that gets called on each time. I want the code to work out the row from a link at the end of the Row (which I also need to figure out how to link to the VBA, I know how to do it via a button but a link at the end of each row would be much better). The Link will say send email. If the user presses this link, then it will select the row the link is on and send the email. Hope that makes sense. I just wanted 1 function this could be called from. Instead of having to duplicate the code each time for each row. Any ideas?
Any good ways of doing this? Please see my code and spreadsheet below.
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;">Sub SendEmail()
Dim objOutlook As Outlook.Application
Set objOutlook =New Outlook.Application
Dim objEmail As Outlook.MailItem
Set objEmail = objOutlook.CreateItem(olMailItem)
objEmail.Subject = Cells(2,1).Text
objEmail.Body ="============"& vbNewLine & Cells(2,3).Text & vbNewLine &"============"& vbNewLine & Cells(2,6).Text
objEmail.To= Cells(2,5).Text
objEmail.SentOnBehalfOfName ="test@test.com"
objEmail.Display
EndSub
</code>I have also attached an example of my spreadsheet. Please note the full on spreadsheet has over 500 records. This is a much condensed version:
https://drive.google.com/folderview...dnekVMREZmVWpPT3h6RmxHRGFRQ3RHYTQ&usp=sharing