Hello All!
Ive done quite a bit in Excel in the past, but have never set up a process that sends mail, so I'm having difficulties. So I've taken coding from http://spreadsheetpage.com/index.php...il_from_excel/ and http://forums.techguy.org/archive-bu...el-quotes.html
...and it works beautifully in excel.
-----------------
Sub SendEMail()
Dim Email As String, Subj As String
Dim Msg As String, URL As String
Email = Cells(2, 11) & vbCrLf & "@email.com"
' Message subject
Subj = Msg & Cells(2, 12) & vbCrLf
' Compose the message
Msg = ""
Msg = ""
' Replace spaces with %20 (hex)
Subj = Application.WorksheetFunction.Substitute(Subj, " ", "%20")
Msg = Application.WorksheetFunction.Substitute(Msg, " ", "%20")
' Replace carriage returns with %0D%0A (hex)
Msg = Application.WorksheetFunction.Substitute(Msg, vbCrLf, "%0D%0A")
' Create the URL
URL = "mailto:" & Email & "?subject=" & Subj & "&body=" & Msg
' Execute the URL (start the email client)
ShellExecute 0&, vbNullString, URL, vbNullString, vbNullString, vbNormalFocus
End Sub
---------------------------
Outlook loads, and its then just a case of hitting the send button, and the email just contains info in the subject line.
The workbook that this sits in is pretty much a database containing employee data in up to 10 columns and 150 rows.
I need to send an email to some of the employees in the list, and to do this, the send button would be located on each row, the macro pulling in info from the respective row (namely the email and the data in column L).
The problem is, as the macro is now, I'd need 150 slightly-different copies of the macro, which is silly.
So, what I need is the macro to be amended so that when you hit the button on the row you want, it just creates the email based on the data in that row. The end goal is to just have one macro, but 150 buttons, one on each row.
I hope thats clear....
I would include a copy of the spreadsheet if I knew how to attach files to the post...
Any help that you guys could offer would be very very appreciated!
Adam
---------------
Excel 2007
Bookmarks