View Full Version : Excel VBA/Email help

02-16-2009, 01:00 PM
I made a really cool sales tracker for my company. Basically the sales reps each get a copy and fill it out based on what they sell. There's then a "Send Email" button which thanks our clients and directs them to a website with a tutorial.
I want to customize the link to send them directly to the product's tutorial site based on what product they bought as seen in the Sales Tracker.
So if ww.they bought Product Q i want them to go to company.com/tutorial/productq.htm and if they bought a Widget i want them to go to ww.company.com/tutorial/widget.htm and so on.
i've been trying to figure this out and it's driving me crazy. can anyone help me out?
would seeing the email portion of my script help? any help is much appreciated.

02-17-2009, 12:58 AM
without sample of code it is hard to guess what possible solution might be.

Is information about sold product stored anywhere??

02-17-2009, 07:53 AM
Sub Mail()
Dim OutApp As Object
Dim OutMail As Object

ActiveSheet.Unprotect Password:=""
SaveSetting "JoshExcel", "WorkbookInfo", "LastSheet", ActiveSheet.Name
strLastSheet = GetSetting("JoshExcel", "WorkbookInfo", "LastSheet")

Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")

br = "<br>"
store = Sheets("Summary").Select
ManagerEmail = Range("P6").Value
location = Range("J6").Value
RSC = Range("B6").Value
CellNum = Range("L6").Value
StoresPhone = Range("M6").Value
RSCEmail = Range("N6").Value
Dear = "<font face=""Times New Roman"">Dear </font>"
strbody1 = "<font face=""Times New Roman""> <br>I just wanted to take a moment to thank you for your recent purchase here at the "
strbody2 = " store.<br><br>" & _
"It's my goal to exceed your expectations and to provide every customer with outstanding service, equipment, and the knowledge necessary for you to stay connected to your world. " & _
"I know that you will enjoy your new equipment and AT&T's service on the network with the best coverage worldwide. If you have any additional questions, please don't hesitate to contact me. Enjoy your new phone!!<br><br>" & _
"If you cannot reach me and you need further assistance on your device, I've inserted a link to our device tutorial site. Click the phone that you have, then pick the topic, and follow the steps and it will tell you everything that you need to know about your device!<br></font>"
link = "<A HREF=""ww.wirelesscom/support/deviceTutorials.do?subscriberType=cing%20""><img src=""ww.wireless.com/support_static_files/images/img-supportPhone-small.gif""></A>"
strbody3 = "<font face=""Times New Roman""><br><br><h3>Thanks again for your business!</h3></font>"
strbody4 = "<font face=""Times New Roman""><font size=""2"">As a reminder, you can earn $125 a year for referring new customers! Just follow the link below and for each friend or family member you refer, who activates a new, qualifying AT&T wireless service, we'll reward each of you with a $25 gift card! You can use the card to buy the latest phone, accessory, ringtone, game, or whatever! You can even use it to pay your AT&T bill!</font>"
refer = "<font face=""Times New Roman""><font size=""2""><a href=""wireless.com/home/landing.do"">Refer A Friend</a></font>"
legal = "<font face=""Times New Roman""><font size=""2""><font color = ""808080"">You have received this email because you are a wireless customer. If you have any questions about how collects uses and protects your personal information as a wireless customer, please visit our Privacy Policy at wireless.att.com/privacy. If you no longer wish to receive future advertising emails , please see the opt-out link within the privacy policy to learn how."
privacy = "<font face=""Times New Roman""><font size=""2""><a href=""ww.wireless.com/privacy/"">Privacy Policy</a></font>"

CellNum = Format(CellNum, "(0) 000-0000")
StoresPhone = Format(StoresPhone, "(0) 000-0000")

On Error GoTo cleanup
For Each Cell In Sheets(strLastSheet).Columns("C").Cells.SpecialCells(xlCellTypeConstants)
If Cell.Value Like "?*@?*.?*" Then
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = Cell.Value
.BCC = ManagerEmail
.Subject = "Thank You"
.HTMLBody = Dear & Cell.Offset(0, 2).Value & "," & strbody1 & location & strbody2 & br & link & strbody3 & RSC & br & location & br & _
"mobile: " & CellNum & br & "store: " & StoresPhone & br & RSCEmail & br & br & strbody4 & br & refer & br & br & legal & br & privacy
End With

Application.Wait (Now + TimeValue("0:00:01"))
Application.SendKeys ("%s")
Application.Wait (Now + TimeValue("0:00:02"))

On Error GoTo 0
Set OutMail = Nothing
End If
Next Cell
Set OutApp = Nothing
Application.ScreenUpdating = True

ActiveSheet.Protect Password:="", _
DrawingObjects:=True, Contents:=True, Scenarios:=True

02-17-2009, 08:02 AM
few more thing where sold product info is kept
and what is the URL format??

02-17-2009, 08:49 AM
product sold is in a drop down list in cell Z14
the list is populated from data validation list from the source named "=Equip_DropDown"
the url is formatted like this:


after the = will be a number depending on the device. so i have vlookup formula to say if the device is 1 then the deviceID is 4509, if the device is 2 then the deviceID is 5893, and so on for all 40 or so devices.

does this make sense? and thanks for your time looking at this!

02-17-2009, 10:37 AM
try using:

'change AA and AB to fit the ranges you have with Device ID and Name
device = WorksheetFunction.VLOOKUP("Z14", "AA1:AB40", 2, 0)
link = "<A HREF=""ww.wirelesscom/support/deviceTutorials.do?deviceId=" _
& device & "><img src=""ww.wireless.com/support_static_files/images/" _
& "img-supportPhone-small.gif""></A>"