RECORD SENT EMAIL

  • Thread starter Thread starter Pam M
  • Start date Start date
P

Pam M

I have a macro that enables the user to send an email from an excel
application. It is attached to a button. Is there a way to add code that
would populate a cell each time that button is used (or the macro is run)? I
would like it to add a line each time, not write it over. I want it to
capture the username, which I have as a function in VB, date, time, and To:.
 
Hi Pam

Use the below proceudre ...WritetoLog..

Sub Macro()
'your code to send mail starts here
'
'your code to send mail ends here

Call WritetoLog(Environ("Username"), "Pam")
'you can replace the username with a variable
'strTo is the mailTo as a string variable

End Sub

Replace the sheet name ...
Sub WritetoLog(strUser As String, strTo As String)
Dim ws As Worksheet, lngRow As Long
Set ws = Worksheets("Sheet1")
lngRow = ws.Cells(Rows.Count, "A").End(xlUp).Row + 1

ws.Range("A" & lngRow) = strUser
ws.Range("B" & lngRow) = strTo
ws.Range("C" & lngRow) = Now
End Sub

If this post helps click Yes
 
Jacob; Very cool! I know enough to be dangerous and am self teaching here.
Couple of questions:

1) In WritetoLog where you have Environ "username", --is that calling my
function for username? If so, if my function is actually called
UserNameWindows, I'm thinking I would replace that with my function name?

2) I tested multiple addresses and code picks up only one. How can I
include all addressees in a string?

3) To capture every user who uses the button, what would my variable be in
place of Pam? An *?

4) If I also wanted to include the body, would I include strBody as string
and add range D?

Thanks so much for your speedy response and your help. Pam
 
Please find the below comments aganist your points and the modified code

Sub Macro()
'your code to send mail starts here
'
'your code to send mail ends here

Dim strReceipients As String
Dim strBody As String
strReceipients = <to string>
strBody = Range("A1").Text
Call WritetoLog(strReceipients, Range("A1"))

End Sub
Sub WritetoLog(strTo As String, strBody As String)
Dim ws As Worksheet, lngRow As Long
Set ws = Worksheets("Sheet1")
lngRow = ws.Cells(Rows.Count, "A").End(xlUp).Row + 1
ws.Range("A" & lngRow) = <call your function here>
ws.Range("B" & lngRow) = strTo
ws.Range("C" & lngRow) = strBody
ws.Range("D" & lngRow) = Now
End Sub

If this post helps click Yes
---------------
Jacob Skaria


Pam M said:
Jacob; Very cool! I know enough to be dangerous and am self teaching here.
Couple of questions:

1) In WritetoLog where you have Environ "username", --is that calling my
function for username? If so, if my function is actually called
UserNameWindows, I'm thinking I would replace that with my function name?
Click to expand...

Sure, you can do that; can call the function within the procedure...
2) I tested multiple addresses and code picks up only one. How can I
include all addressees in a string?
Click to expand...

If you have the to addresses as a string pass that to strReceipients or
replace that with your variable..If you have it in a range you can loop and
store that to a variable
3) To capture every user who uses the button, what would my variable be in
place of Pam? An *?
Click to expand...
Pam was in place of strTo....In the modified one pass the receipients to the
variable
4) If I also wanted to include the body, would I include strBody as string
and add range D?
Click to expand...

Check the modified code..
Thanks so much for your speedy response and your help. Pam
Click to expand...

You are always welcome..
 
strReceipients = "You need to enter the receipeints within this doule quotes"

If this post helps click Yes
 
Its variable each time the button is used. If I want all recipients, what
would I enter?
 
Pass your variable with the recepients.. or if it is in a range cell A1
strReceipients = Range("A1")

From previous post
2) I tested multiple addresses and code picks up only one. How can I
include all addressees in a string?
Click to expand...
If you have the to addresses as a string pass that to strReceipients or
replace that with your variable..If you have it in a range you can loop and
store that to a variable

If this post helps click Yes
 
Back
Top