Auto email send to Recipients update in spreadsheet using Excel

[Closed]
Report
Posts
4
Registration date
Wednesday October 2, 2013
Status
Member
Last seen
October 7, 2013
-
Posts
2779
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
July 29, 2021
-
Hello,

I'm new to this forum. Currently I have a shared workbook that link to multiple user for update. I have a column for having the person (example: A) who enter the data and information and the column next to it will be the person who need to review the data information that A is entered. I would like to set a macro in excel where whenever A is enter the info inside the spreadsheet, once it save an auto email will send to the person who need to review the data entry and CC the person who enter in the email loop.

Appreciate the expert help here as I'm not strong in vb or macro, but I know this can be done with some coding in macro.

Thanks you,
limktin

6 replies

Posts
2779
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
July 29, 2021
468
Hi Limktin,

Assuming that review person's email address is the last entry in column B.
Change:
newmsg.Recipients.Add ("sampleemailaddresshere@email.com")
into:
newmsg.Recipients.Add (Range("B"&rows.count).end(xlup).value)

Best regards,
Trowa
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Posts
2779
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
July 29, 2021
468
Hi Limktin,

Try this:
Dim lRow, x As Integer
lRow = Range("B" & Rows.Count).End(xlUp).Row
For Each cell In Range(Cells(lRow, "A"), Cells(lRow, "L"))
If cell.Value <> vbNullString Then
x = x + 1
End If
Next cell
If x <> 12 Then MsgBox "Please check the last entry. There is at least 1 empty cell found in column A:L", , "Missing data"

Best regards,
Trowa
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Posts
2779
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
July 29, 2021
468
Sorry Limktin, didn't thought it through.

Try this:
Dim lRow, x As Integer
lRow = Range("B" & Rows.Count).End(xlUp).Row
For Each cell In Range(Cells(lRow, "A"), Cells(lRow, "L"))
If cell.Value <> vbNullString Then
x = x + 1
End If
Next cell
If x <> 12 Then
MsgBox "Please check the last entry. There is at least 1 empty cell found in column A:L", , "Missing data"
Exit Sub
End If

You can always add some text to instruct users to hit the 'Send Email' button again.

Best regards,
Trowa

Monday, Tuesday and Thursday are usually the days I'll respond. Bear this in mind when awaiting a reply.
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Posts
4
Registration date
Wednesday October 2, 2013
Status
Member
Last seen
October 7, 2013

i see this coding in this forum, it's work for me but the problem is how to make the Recipients as variable and copy from spreadsheet once someone enter, and also add in person (also a variable) enter in email sent


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)

Dim answer As String

answer = MsgBox("This is where you put the text to prompt the user if he wants to save or not" vbYesNo, "here is the title of that box")

If answer = vbNo Then Cancel = True
If answer = vbYes Then
'open outlook type stuff
Set OutlookApp = CreateObject("Outlook.Application")
Set OlObjects = OutlookApp.GetNamespace("MAPI")
Set newmsg = OutlookApp.CreateItem(olMailItem)
'add recipients
'newmsg.Recipients.Add ("Name Here")
newmsg.Recipients.Add ("sampleemailaddresshere@email.com")
'add subject
newmsg.Subject = "Subject line of auto email here"
'add body
newmsg.Body = "body of auto email here"
newmsg.Display 'display
newmsg.Send 'send message
'give conformation of sent message
MsgBox "insert confirmation box test here", , "title of confirmation box"



End If


'save the document
'Me.Worksheets.Save

End Sub
Posts
4
Registration date
Wednesday October 2, 2013
Status
Member
Last seen
October 7, 2013

thanks, it's do work.

If I would like to set a condition that ensure there is info entry from column A to L example, if not it will prompt message. Would able to advice for the code?
Posts
4
Registration date
Wednesday October 2, 2013
Status
Member
Last seen
October 7, 2013

Thanks TrowaD again.

the codes looks good, so after the message box prompt out, program should stop and allow the entry person to correct the infor, after that only re-click on 'Send Email' button to send out mail. But the coding above after message box click ok, email still will send out to the person but it's not suppose to me, perhaps we still need one or two sentence to work out on that.

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!