Thank you so much for posting this VBA Code and instructions. When I found it I felt like I had won the lotto. However I am stuck on something so I'm hoping you can help (I'm new to VBA, only have very basic understanding).
I've copied the code and changed the cell and cell value to pick from a range if a criteria is met. I have tried and tested and it works and I received an email to outlook based on the criteria.
1) However, I cannot seem to figure out how to get the VBA code to run automatically when I open up the excel worksheet, rather than having to click on the VBA application and select run. Could you advise if there is an additional prompt to type into the VBA code above that will do this or does it have to be done separately.
2) Also is there a way to get the VBA code to send a mail to a person if the due date is yes for a certain item as shown in example below.
email hidden column
Procedure no.1 due date yes
Procedure no. 2 due date no
I would have numerous people in the spreadsheet (going across horizontally in a row) and 'Yes' could be highlighted for various overdue procedures (listed vertically in column A. Is there a way to create a VBA code that runs for something like this - if 'Yes' for 'Person 1', then email 'person 1' with 'procedure no #' (or numbers) and due date(s). Being able to list in the email all the procedures and their subsequent due dates.
I wouldn't mind if I had to set a separate VBA code for each person as long as it sent a mail of all the documents overdue for that person and the due dates.
Hoping you can help
- To post as a guest, your comment is unpublished.· 3 years agoDear Ann,
Please try the below VBA code. Thank you for your comment.
Dim xRg As Range
Dim xCell As Range
Dim I As Long
Dim xRows As Long
Dim xCols As Long
Dim xVal As String
Dim xOutApp As Object
Dim xOutMail As Object
Dim xMailBody As String
On Error Resume Next
Set xRg = Application.InputBox("Select the range contains the cell value you will send emails based on:", "Kutools for Excel", Selection.Address, , , , , 8)
If xRg Is Nothing Then Exit Sub
xRows = xRg.Rows.Count
xCols = xRg.Columns.Count
For I = 1 To xRows
Set xCell = xRg(I, xCols)
If xCell.Value = "Yes" Then
Set xOutApp = CreateObject("Outlook.Application")
Set xOutMail = xOutApp.CreateItem(0)
xMailBody = "Hi there" & vbNewLine & vbNewLine & _
"This is your information: " & vbNewLine & xCell.Offset(0, -1).Text & vbNewLine & xCell.Offset(0, -2).Text
.To = xCell.Offset(0, -4).Text
.Subject = "send by cell value test"
.Body = xMailBody
.Display 'or use .Send
On Error GoTo 0
Set xOutMail = Nothing
Set xOutApp = Nothing
- To post as a guest, your comment is unpublished.· 3 years agoWhere exactly do we insert this code?
- To post as a guest, your comment is unpublished.· 3 years agoGood day,
You need to place the code into the worksheet's code window.
Open the Microsoft Visual Basic for Applications window, double click the sheet name in the left pane to open the code editor.
- To post as a guest, your comment is unpublished.· 3 years agoCrystal,
This replaces the following code:
Dim xRg As Range
Dim xRgEach As Range
Dim xEmail_Subject, xEmail_Send_Form,;etc.