VBA Code to Copy Data from Cells to Email if Another Cell Equals Current Date

tkc5869

New Member
Joined
Mar 25, 2022
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Hi all,

I'm new to VBA but am trying to automate a spreadsheet to automatically send out an email when a cell value equals "Complete". The flow I have in mind is that when a dropdown value is selected "Complete", then the data from another cell will be copied into an email body and automatically sent to several addresses. So far, I only have subroutines set up to generate the email minus the data from the cells I need. Also, the range I have selected is for all cells in column J, but there may be a simpler way to define this.

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = True
If Not Intersect(Target, Range("J1:J30000")) Is Nothing Then
Select Case Target.Value
Case "Complete"
SendOrderStatusComplete

End Select
End If

If Not Intersect(Target, Range("J1:J30000")) Is Nothing Then
Target.Offset(0, 1) = Date
End If

End Sub
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here (de-sensitized if necessary). We need to know which cell you want to add to the body of the email and where the email address of the recipients are located. We also need to know the subject of the email and what other text you may want to add to the body. It would help if you could attach a screen shot of what the complete email would look like.
 
Upvote 0
It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here (de-sensitized if necessary). We need to know which cell you want to add to the body of the email and where the email address of the recipients are located. We also need to know the subject of the email and what other text you may want to add to the body. It would help if you could attach a screen shot of what the complete email would look like.
I'll post the XL2BBCode below. As well as an example of an email message with Cell names representing the data needed input in the message.

Dummy Workbook.xlsm
CDEFGHIJK
1Planner to UpdatePlanner to Update
2Build WeekSEQUENCE WORK ORDER #Planner NotesCust. Commit GI DtCust. Req GI DtOrd. Cr. DateOrder StatusCompleted
344641610473816911/22/2111/22/218/16/20213/25/2021
44464121046465603/25/2212/16/2112/2/20213/25/2021
54464131046187041/7/221/7/223/25/2021
64464111047407521/20/221/13/2210/10/20213/25/2021
74464128104747918RELABEL JOB1/20/221/20/2210/8/20213/25/2021
844641411047495672/11/222/11/2212/17/20213/25/2021
94464181047412973/30/223/16/2211/18/20213/25/2021
104464139104749376RELABEL JOB3/17/223/17/222/23/20223/25/2021
114464191047412983/23/223/23/221/28/20223/26/2021
1244641451047509214/8/223/24/223/15/20223/26/2021
1344641156039611special slit request3/25/223/25/223/18/20223/26/2021
1444641186039614special slit request3/25/223/25/223/18/20223/26/2021
1544641196039615special slit request3/25/223/25/223/18/20223/26/2021
1644641206039616special slit request3/25/223/25/223/18/20223/26/2021
1744641421047510403/25/223/25/2212/14/20213/26/2021
1844641241047459953/28/223/28/223/21/20223/26/2021
1944641276039610special slit request3/28/223/28/223/17/20223/26/2021
2044641431047509393/28/223/28/221/14/20223/26/2021
214464171047412963/29/223/29/2212/6/2021
224464140104749565EXPEDITE3/29/223/29/222/3/2022
2344641101047412993/30/223/30/223/9/2022
2444641441047509223/30/223/30/222/14/2022
2544641261047477764/5/224/1/222/3/2022
2644641291047492044/4/224/4/222/16/2022
2744641301047492064/7/224/4/222/20/2022
2844641251047460854/7/224/7/223/18/2022
2944641311047492084/7/224/7/222/21/2022
304464141047319514/11/224/11/2212/23/2021
3144641111047413204/14/224/14/222/25/2022
3244641321047492094/15/224/15/222/28/2022
3344641121047413214/21/224/18/223/6/2022
3444641331047492104/21/224/18/223/6/2022
3544641341047492114/21/224/18/223/6/2022
3644641351047492124/21/224/18/223/6/2022
3744641361047492144/21/224/18/223/6/2022
384464146104751142ASAP4/18/224/18/2212/9/2021
394464151047364714/19/224/19/223/3/2022
4044641131047413224/19/224/19/221/24/2022
4144641371047492154/19/224/19/223/3/2022
4244641141047413234/21/224/21/222/8/2022
4344641381047492164/29/224/29/223/15/2022
Sheet1
Cells with Data Validation
CellAllowCriteria
J3:J43List=Sheet2!$A$1:$A$3
 

Attachments

  • Dummy Email.PNG
    Dummy Email.PNG
    7.3 KB · Views: 9
Upvote 0
I noticed that you don't have a drop down in column J. Do you want to create the email when a completed date is entered in column I?
 
Upvote 0
I noticed that you don't have a drop down in column J. Do you want to create the email when a completed date is entered in column I?
I noticed that you don't have a drop down in column J. Do you want to create the email when a completed date is entered in column I?
I do have a drop down column in column J. When "Completed" is selected in the drop down, it will generate the email.
 
Upvote 0
Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Select "Complete" in column J.
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Column <> 10 Then Exit Sub
    Dim OutApp As Object, OutMail As Object
    Application.ScreenUpdating = False
    If Target = "Complete" Then
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)
        With OutMail
            .To = ""
            .Subject = "Slitting Job# " & Range("E" & Target.Row).Value & " Completed"
            .HTMLBody = "This is a notification that Job# " & Range("E" & Target.Row).Value & " has been completed."
            .Display
        End With
    End If
    Application.ScreenUpdating = True
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,306
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top