Auto email send to Recipients update in spreadsheet using Excel Closed

Question

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

limktin · Answer

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

TrowaD · Answer

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

limktin · Answer

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?

TrowaD · Answer

Hi Limktin, Try this: Dim lRow, x As IntegerlRow = Range("B" & Rows.Count).End(xlUp).RowFor Each cell In Range(Cells(lRow, "A"), Cells(lRow, "L")) If cell.Value <> vbNullString Then x = x + 1 End IfNext cellIf 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

limktin · Answer

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.

TrowaD · Answer

Sorry Limktin, didn't thought it through. Try this: Dim lRow, x As IntegerlRow = Range("B" & Rows.Count).End(xlUp).RowFor Each cell In Range(Cells(lRow, "A"), Cells(lRow, "L")) If cell.Value <> vbNullString Then x = x + 1 End IfNext cellIf x <> 12 Then MsgBox "Please check the last entry. There is at least 1 empty cell found in column A:L", , "Missing data" Exit SubEnd 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.

Auto email send to Recipients update in spreadsheet using Excel

6 responses

Similar discussions

Subscribe To Our Newsletter!