Prompt Title: Create Excel Macro.

Yes, I can help you with that. Here's the VB code for the macro:

mathematicaCopy code
Sub CreateEmails()

Dim OutApp As Object
Dim OutMail As Object
Dim rng As Range
Dim cell As Range

Set OutApp = CreateObject("Outlook.Application")
Set rng = Range("A2:E" & Cells(Rows.Count, "A").End(xlUp).Row)

For Each cell In rng

    Set OutMail = OutApp.CreateItem(0)

    With OutMail
        .To = cell.Offset(0, 1).Value
        .Subject = "Confirmation needed for opportunity at " & cell.Offset(0, 2).Value
        .HTMLBody = "Hi " & cell.Value & "," & "<br><br>" & _
        "Hope you are well! We noticed you created an opportunity at " & cell.Offset(0, 2).Value & " for " & cell.Offset(0, 3).Value & " (" & cell.Offset(0, 4).Value & ")" & _
        "at a " & Chr(34) & cell.Offset(0, 5).Value & Chr(34) & " stage in the last 7 days. Could you confirm if this data in the system is correct?" & _
        "<br><br>" & "If yes, please provide us with a little context (2-3 brief bullet points is all) on the nature of the work so that we may share the details with Arthur (i.e. what we are proposing to the Client, are we collaborating across any of our PS horizontals [SPEED, Practices, Partners], are we competing for the work, if so against whom?)." & _
        "<br><br>" & "Thanks in advance for your prompt reply, if possible by EOD Thursday, April 20th would be really appreciated."
        .Display
    End With

Next cell

Set OutMail = Nothing
Set OutApp = Nothing

End Sub

You need to make sure to update the range variable rng to the correct range in your Excel sheet. In the code above, it assumes that the data starts from cell A2 and ends in column E.

Also, note that this code assumes that you have Outlook installed on your computer and that it is the default email program. If you use a different email program or if Outlook is not installed, you may need to modify the code accordingly.

Yes, it's definitely possible to create a button on the sheet that runs the macro when clicked. Here are the steps to create a button and assign the macro to it:

1. First, make sure that the Developer tab is visible on the ribbon. If it's not, go to File > Options > Customize Ribbon, and check the box next to Developer.

2. Click on the Developer tab, and then click on the Insert button in the Controls group.

3. In the Form Controls section, click on the Button icon (it looks like a rectangle with a label).

4. Draw a rectangle on the sheet where you want the button to appear. You can change the button text by right-clicking on it and selecting Edit Text.

5. Right-click on the button, and select Assign Macro.

6. In the Assign Macro dialog box, select the macro that you just created (it should be named CreateEmails), and click OK.

7. Close the dialog box, and then click on the button to run the macro.

That's it! Now you have a button on your sheet that will run the macro when clicked.

Here are the steps to create the macro in Visual Basic for Applications (VBA):

1. Press Alt + F11 to open the VBA Editor.

2. In the VBA Editor, go to Insert > Module to create a new module.

3. Copy and paste the VB code that I provided earlier into the new module.

4. Save the macro by clicking on the Save button in the toolbar, or by pressing Ctrl + S.

5. Close the VBA Editor.

Now you should have a macro named "CreateEmails" that will create the emails based on the data in your Excel sheet. You can assign this macro to a button on the sheet as I explained in my previous response.

Sure! Here's the modified macro code that includes the condition to check the "Dump Date" column:

mathematicaCopy code
Sub CreateEmails()

Dim OutApp As Object
Dim OutMail As Object
Dim rng As Range
Dim cell As Range

Set OutApp = CreateObject("Outlook.Application")
Set rng = Range("A2:F" & Cells(Rows.Count, "A").End(xlUp).Row)

For Each cell In rng
    If cell.Offset(0, 5).Value = Date Then ' Check if Dump Date = Today's date
        Set OutMail = OutApp.CreateItem(0)

        With OutMail
            .To = cell.Offset(0, 1).Value
            .Subject = "Confirmation needed for opportunity at " & cell.Offset(0, 2).Value
            .HTMLBody = "Hi " & cell.Value & "," & "<br><br>" & _
            "Hope you are well! We noticed you created an opportunity at " & cell.Offset(0, 2).Value & " for " & cell.Offset(0, 3).Value & " (" & cell.Offset(0, 4).Value & ")" & _
            "at a " & Chr(34) & cell.Offset(0, 5).Value & Chr(34) & " stage in the last 7 days. Could you confirm if this data in the system is correct?" & _
            "<br><br>" & "If yes, please provide us with a little context (2-3 brief bullet points is all) on the nature of the work so that we may share the details with Arthur (i.e. what we are proposing to the Client, are we collaborating across any of our PS horizontals [SPEED, Practices, Partners], are we competing for the work, if so against whom?)." & _
            "<br><br>" & "Thanks in advance for your prompt reply, if possible by EOD Thursday, April 20th would be really appreciated."
            .Display
        End With
    End If
Next cell

Set OutMail = Nothing
Set OutApp = Nothing

End Sub

This code checks if the value in the "Dump Date" column (column F in this example) is equal to today's date before creating the email. If the condition is true, the email is created; otherwise, the macro moves on to the next row.

Make sure to update the range variable rng to the correct range in your Excel sheet. In the code above, it assumes that the data starts from cell A2 and ends in column F.

You're welcome! Let me know if you have any questions or if you need further assistance.

Yes, in the macro code, the line .Display will display the email message as a draft in Outlook so that you can review it before sending. Once you are satisfied with the email, you can send it manually or programatically by calling the .Send method instead of the .Display method. Here's an updated code that sends the email automatically:

mathematicaCopy code
Sub CreateEmails()

Dim OutApp As Object
Dim OutMail As Object
Dim rng As Range
Dim cell As Range

Set OutApp = CreateObject("Outlook.Application")
Set rng = Range("A2:F" & Cells(Rows.Count, "A").End(xlUp).Row)

For Each cell In rng
    If cell.Offset(0, 5).Value = Date Then ' Check if Dump Date = Today's date
        Set OutMail = OutApp.CreateItem(0)

        With OutMail
            .To = cell.Offset(0, 1).Value
            .Subject = "Confirmation needed for opportunity at " & cell.Offset(0, 2).Value
            .HTMLBody = "Hi " & cell.Value & "," & "<br><br>" & _
            "Hope you are well! We noticed you created an opportunity at " & cell.Offset(0, 2).Value & " for " & cell.Offset(0, 3).Value & " (" & cell.Offset(0, 4).Value & ")" & _
            "at a " & Chr(34) & cell.Offset(0, 5).Value & Chr(34) & " stage in the last 7 days. Could you confirm if this data in the system is correct?" & _
            "<br><br>" & "If yes, please provide us with a little context (2-3 brief bullet points is all) on the nature of the work so that we may share the details with Arthur (i.e. what we are proposing to the Client, are we collaborating across any of our PS horizontals [SPEED, Practices, Partners], are we competing for the work, if so against whom?)." & _
            "<br><br>" & "Thanks in advance for your prompt reply, if possible by EOD Thursday, April 20th would be really appreciated."
            .Send ' Send the email automatically
        End With
    End If
Next cell

Set OutMail = Nothing
Set OutApp = Nothing

End Sub

In this code, the line .Send will automatically send the email message without showing it as a draft. Be careful when using this line, as it sends the email immediately and you won't have a chance to review it before it's sent.

If your date data is in the first column, you would need to modify the code to check the Dump Date in the first column. Here's the modified code with the offset changed to Offset(0, 4) instead of Offset(0, 5):

mathematicaCopy code
Sub CreateEmails()

Dim OutApp As Object
Dim OutMail As Object
Dim rng As Range
Dim cell As Range

Set OutApp = CreateObject("Outlook.Application")
Set rng = Range("A2:F" & Cells(Rows.Count, "A").End(xlUp).Row)

For Each cell In rng
    If cell.Value = Date And cell.Offset(0, 4).Value = Date Then ' Check if Dump Date = Today's date
        Set OutMail = OutApp.CreateItem(0)

        With OutMail
            .To = cell.Offset(0, 1).Value
            .Subject = "Confirmation needed for opportunity at " & cell.Offset(0, 2).Value
            .HTMLBody = "Hi " & cell.Offset(0, 0).Value & "," & "<br><br>" & _
            "Hope you are well! We noticed you created an opportunity at " & cell.Offset(0, 2).Value & " for " & cell.Offset(0, 3).Value & " (" & cell.Offset(0, 4).Value & ")" & _
            "at a " & Chr(34) & cell.Offset(0, 5).Value & Chr(34) & " stage in the last 7 days. Could you confirm if this data in the system is correct?" & _
            "<br><br>" & "If yes, please provide us with a little context (2-3 brief bullet points is all) on the nature of the work so that we may share the details with Arthur (i.e. what we are proposing to the Client, are we collaborating across any of our PS horizontals [SPEED, Practices, Partners], are we competing for the work, if so against whom?)." & _
            "<br><br>" & "Thanks in advance for your prompt reply, if possible by EOD Thursday, April 20th would be really appreciated."
            .Display ' Display the email as a draft for review
        End With
    End If
Next cell

Set OutMail = Nothing
Set OutApp = Nothing

End Sub

In this modified code, the If statement now checks if both the Dump Date (in the first column) and the current date are equal to today's date. Also, the Offset for the name and account name fields are changed to Offset(0, 1) and Offset(0, 2) respectively.

Thank you for sharing the screenshot of your Excel data. Based on your screenshot, it looks like your Dump Date column is in column G (7th column).

Therefore, you would need to modify the If statement to check the Dump Date in column G using the Offset property with Offset(0, 6). Also, the Offset for the name and account name fields are changed to Offset(0, 2) and Offset(0, 3) respectively.

Here's the modified code:

mathematicaCopy code
Sub CreateEmails()

Dim OutApp As Object
Dim OutMail As Object
Dim rng As Range
Dim cell As Range

Set OutApp = CreateObject("Outlook.Application")
Set rng = Range("A2:G" & Cells(Rows.Count, "A").End(xlUp).Row)

For Each cell In rng
    If cell.Value = Date And cell.Offset(0,

Thank you for letting me know. Based on your information, here's the modified code that will check for the dump date in column A, and retrieve the corresponding data from columns C, D, G, P, and Q.

mathematicaCopy code
Sub CreateEmails()

Dim OutApp As Object
Dim OutMail As Object
Dim rng As Range
Dim cell As Range

Set OutApp = CreateObject("Outlook.Application")
Set rng = Range("A2:Q" & Cells(Rows.Count, "A").End(xlUp).Row)

For Each cell In rng
    If cell.Value = Date And cell.Offset(0, 2).Value <> "" And cell.Offset(0, 3).Value <> "" Then
        Set OutMail = OutApp.CreateItem(0)
        With OutMail
            .To = ""
            .CC = ""
            .BCC = ""
            .Subject = ""
            .Body = "Hi " & cell.Offset(0, 2).Value & "," & vbNewLine & vbNewLine & "Hope you are well! We noticed you created an opportunity at " & cell.Offset(0, 3).Value & " for " & cell.Offset(0, 15).Value & " (" & cell.Offset(0, 16).Value & ") at a “" & cell.Offset(0, 6).Value & "” stage in the last 7 days. Could you confirm if this data in the system is correct?" & vbNewLine & vbNewLine & "If yes, please provide us with a little context (2-3 brief bullet points is all) on the nature of the work so that we may share the details with Arthur (i.e. what we are proposing to the Client, are we collaborating across any of our PS horizontals [SPEED, Practices, Partners], are we competing for the work, if so against whom?)." & vbNewLine & vbNewLine & "Thanks in advance for your prompt reply, if possible by EOD Thursday, April 20th would be really appreciated."
            .Display
        End With
    End If
Next cell

Set OutMail = Nothing
Set OutApp = Nothing

End Sub

In this modified code, the dump date is checked in column A using cell.Value = Date, and the corresponding data from columns C, D, G, P, and Q is retrieved using the Offset property with Offset(0, 2), Offset(0, 3), Offset(0, 6), Offset(0, 15), and Offset(0, 16) respectively.

Sure! Here is the modified code that includes the changes you requested:

mathematicaCopy code
Sub CreateEmails()

Dim OutApp As Object
Dim OutMail As Object
Dim rng As Range
Dim cell As Range
Dim dumpDate As Date

Set OutApp = CreateObject("Outlook.Application")
Set rng = Range("A2:Q" & Cells(Rows.Count, "A").End(xlUp).Row)

For Each cell In rng
    If cell.Value = Date And cell.Offset(0, 2).Value <> "" And cell.Offset(0, 3).Value <> "" Then
        Set OutMail = OutApp.CreateItem(0)
        With OutMail
            .To = ""
            .CC = ""
            .BCC = ""
            .Subject = "Response Required | " & cell.Offset(0, 3).Value & " | New Opportunity"
            .Body = "Hi " & cell.Offset(0, 2).Value & "," & vbNewLine & vbNewLine & "Hope you are well! We noticed you created an opportunity at " & cell.Offset(0, 3).Value & " for " & cell.Offset(0, 15).Value & " (" & cell.Offset(0, 16).Value & ") at a “" & cell.Offset(0, 6).Value & "” stage in the last 7 days. Could you confirm if this data in the system is correct?" & vbNewLine & vbNewLine & "If yes, please provide us with a little context (2-3 brief bullet points is all) on the nature of the work so that we may share the details with Arthur (i.e. what we are proposing to the Client, are we collaborating across any of our PS horizontals [SPEED, Practices, Partners], are we competing for the work, if so against whom?)." & vbNewLine & vbNewLine & "Thanks in advance for your prompt reply, if possible by EOD " & Format(cell.Value + 1, "Long Date") & " would be really appreciated."
            .Display
        End With
    End If
Next cell

Set OutMail = Nothing
Set OutApp = Nothing

End Sub

In this modified code, the subject of the email is changed to "Response Required | <Account Name> | New Opportunity" using cell.Offset(0, 3).Value for the account name in column D.

Additionally, the date in the body of the email is updated to use Format(cell.Value + 1, "Long Date") instead of "April 20th" to display the dump date + 1 day in the same format as the Long Date.

Yes, you can add a hyperlink to the "opportunity at <Account Name>" text in the email body by using the .HTMLBody property instead of the .Body property. Here's the modified code:

mathematicaCopy code
Sub CreateEmails()

Dim OutApp As Object
Dim OutMail As Object
Dim rng As Range
Dim cell As Range
Dim dumpDate As Date

Set OutApp = CreateObject("Outlook.Application")
Set rng = Range("A2:Q" & Cells(Rows.Count, "A").End(xlUp).Row)

For Each cell In rng
    If cell.Value = Date And cell.Offset(0, 2).Value <> "" And cell.Offset(0, 3).Value <> "" Then
        Set OutMail = OutApp.CreateItem(0)
        With OutMail
            .To = ""
            .CC = ""
            .BCC = ""
            .Subject = "Response Required | " & cell.Offset(0, 3).Value & " | New Opportunity"
            .HTMLBody = "Hi " & cell.Offset(0, 2).Value & "," & "<br><br>" & _
                        "Hope you are well! We noticed you created an opportunity at <a href='" & cell.Offset(0, 16).Value & "'>" & cell.Offset(0, 3).Value & "</a> for " & cell.Offset(0, 15).Value & " at a “" & cell.Offset(0, 6).Value & "” stage in the last 7 days. Could you confirm if this data in the system is correct?" & "<br><br>" & _
                        "If yes, please provide us with a little context (2-3 brief bullet points is all) on the nature of the work so that we may share the details with Arthur (i.e. what we are proposing to the Client, are we collaborating across any of our PS horizontals [SPEED, Practices, Partners], are we competing for the work, if so against whom?)." & "<br><br>" & _
                        "Thanks in advance for your prompt reply, if possible by EOD " & Format(cell.Value + 1, "Long Date") & " would be really appreciated."
            .Display
        End With
    End If
Next cell

Set OutMail = Nothing
Set OutApp = Nothing

End Sub

In this modified code, the HTMLBody property is used to create the email body with a hyperlink. The hyperlink is created by adding <a> tags around the <Account Name> text, with the href attribute set to the link in column Q.

Note that because we're using the HTMLBody property, we also need to add the <br> tag for line breaks instead of vbNewLine.

Yes, it is possible to specify the sender's email address in the macro code. You can modify the SendUsingAccount property of the MailItem object to specify the email address to be used for sending the email.

Here's the modified code that sets the sender email address to "[email protected]":

vbCopy code
Sub CreateEmailDrafts()
    
    Dim olApp As Object
    Dim olMail As Object
    Dim olAccount As Outlook.Account
    
    Dim i As Long
    Dim lastRow As Long
    Dim todayDate As Date
    
    Set olApp = CreateObject("Outlook.Application")
    Set olAccount = olApp.Session.Accounts.Item(1) ' change index as needed
    
    todayDate = Date
    
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    
    For i = 2 To lastRow
        If Cells(i, 1).Value = todayDate Then
            Set olMail = olApp.CreateItem(0)
            With olMail
                .SentOnBehalfOfName = "[email protected]"
                .To = Cells(i, 2).Value
                .Subject = "Response Required | " & Cells(i, 4).Value & " | New Opportunity"
                .HTMLBody = "<p>Hi " & Cells(i, 3).Value & ",</p>" _
                            & "<p>Hope you are well! We noticed you created an opportunity at <a href='" & Cells(i, 16).Value & "'>" & Cells(i, 4).Value & "</a> for " & Cells(i, 15

I apologize for the confusion. If you want the emails to be sent from the email address "abhis

I apologize for the confusion. If you want the emails to be sent from the email address "[email protected]" directly, you can modify the olMail.SentOnBehalfOfName property to olMail.SentFrom property.

Here's the modified code:

vbCopy code
Sub CreateEmailDrafts()
    
    Dim olApp As Object
    Dim olMail As Object
    Dim olAccount As Outlook.Account
    
    Dim i As Long
    Dim lastRow As Long
    Dim todayDate As Date
    
    Set olApp = CreateObject("Outlook.Application")
    Set olAccount = olApp.Session.Accounts.Item(1) ' change index as needed
    
    todayDate = Date
    
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    
    For i = 2 To lastRow
        If Cells(i, 1).Value = todayDate Then
            Set olMail = olApp.CreateItem(0)
            With olMail
                .SentOnBehalfOfName = olAccount.SmtpAddress
                .SentFrom = "[email protected]"
                .To = Cells(i, 2).Value
                .Subject = "Response Required | " & Cells(i, 4).Value & " | New Opportunity"
                .HTMLBody = "<p>Hi " & Cells(i, 3).Value & ",</p>" _
                            & "<p>Hope you are well! We noticed you created an opportunity at <a href='" & Cells(i, 16).Value & "'>" & Cells(i, 4).Value & "</a> for " & Cells(i, 15) & " (" & Cells(i, 16).Value & ") at a “" & Cells(i, 7).Value & "” stage in the last 7 days. Could you confirm if this data in the system is correct?</p>" _
                            & "<p>If yes, please provide us with a little context (2-3 brief bullet points is all) on the nature of the work so that we may share the details with Arthur (i.e. what we are proposing to the Client, are we collaborating across any of our PS horizontals [SPEED, Practices, Partners], are we competing for the work, if so against whom?).</p>" _
                            & "<p>Thanks in advance for your prompt reply, if possible by EOD " & Format(Cells(i, 1).Value + 1, "mmmm dd\, yyyy") & " would be really appreciated.</p>"
                .Display
            End With
        End If
    Next i
    
    Set olMail = Nothing
    Set olAccount = Nothing
    Set olApp = Nothing
    
End Sub

This code uses the olAccount.SmtpAddress property to set the "SentOnBehalfOfName" property to the SMTP address of the account that you want to use for sending the email. And, it sets the "SentFrom" property to the email address "[email protected]" to send the email directly from that email address.

Yes, you can use the same macro on another sheet in the same Excel file as long as the sheet has the same structure and the data is arranged in the same columns as the original sheet.

You can simply change the sheet name in the following line of code to the name of the new sheet:

javaCopy code
Set ws = ThisWorkbook.Worksheets("Sheet1")

For example, if the new sheet is called "Sheet2", you would change the line of code to:

javaCopy code
Set ws = ThisWorkbook.Worksheets("Sheet2")

This will ensure that the macro reads the data from the correct sheet.

No, you do not have to create two macros for two buttons on different sheets in the same Excel file.

You can use the same macro for both buttons and simply change the sheet name in the code to match the name of the sheet with the data you want to use for each button.

For example, if one button is on Sheet1 and the other is on Sheet2, you can change the sheet name in the following line of code:

javaCopy code
Set ws = ThisWorkbook.Worksheets("Sheet1")

to:

javaCopy code
Set ws = ThisWorkbook.Worksheets("Sheet2")

for the macro associated with the button on Sheet2.

This way, the macro will read the data from the correct sheet based on the button that is clicked.

The error "User defined type not defined" for the Dim olAccount line means that the Outlook object library is not referenced in your VBA project.

To add the reference to Outlook object library, follow these steps:

1. Open the VBA editor by pressing ALT+F11.
2. In the VBA editor, go to Tools > References.
3. In the References dialog box, scroll down and find "Microsoft Outlook XX.X Object Library" (where XX.X is the version of Outlook installed on your computer).
4. Check the box next to the reference, and click OK.
5. Save your VBA project.

This should resolve the "User defined type not defined" error for the Dim olAccount line.

If you're getting an error message saying that the macro cannot run, it may be due to one of the following reasons:

1. Macro Security Settings: Excel has a macro security setting that can prevent macros from running. Go to File > Options > Trust Center > Trust Center Settings > Macro Settings and ensure that "Enable all macros" is selected.

2. Missing Reference: Make sure that all the necessary references are selected in your VBA project. Go to Tools > References and ensure that all the required references are selected.

3. Macro Name: Make sure that the macro name you are trying to run is spelled correctly and exists in the correct workbook and module.

4. Protected Workbook: If the workbook is password-protected or has restricted access, it may prevent macros from running. Try opening the workbook with full access or removing the password protection.

If none of these solutions work, please provide more details on the error message and any other information that may be helpful in troubleshooting the issue.

It seems like you have left the .To property empty, which would cause the error. Please replace the empty quotes with the email address you want to send the email to. For example, if you want to send it to [email protected], the line should look like:

perlCopy code
.To = "[email protected]"

Also, make sure you have Outlook installed and that your macro settings are properly configured to allow macros to run.

Sure, I'll keep that in mind for future code editing. Let me know if you have any questions or if there's anything else I can help you with.

Yes, you can include the first word in the name field in the email body. You can modify the .HTMLBody line in the macro to include the first word of the name field using string manipulation.

Here's an example of how you can modify the line to include the first word of the name field:

mathematicaCopy code
.HTMLBody = "Hi " & Left(cell.Offset(0, 2).Value, InStr(cell.Offset(0, 2).Value, " ") - 1) & "," & "<br><br>" & _

This line uses the Left function to extract the first word of the name field using the InStr function to find the position of the first space in the name field. The -1 in the Left function is used to exclude the space itself from the extracted string.

Here's the full modified macro with the above line included:

mathematicaCopy code
Sub CreateEmails()

Dim OutApp As Object
Dim OutMail As Object
Dim rng As Range
Dim cell As Range
Dim dumpDate As Date

Set OutApp = CreateObject("Outlook.Application")
Set rng = Range("A2:Q" & Cells(Rows.Count, "A").End(xlUp).Row)

For Each cell In rng
    If cell.Value = Date And cell.Offset(0, 2).Value <> "" And cell.Offset(0, 3).Value <> "" Then
        Set OutMail = OutApp.CreateItem(0)
        With OutMail
            .To = ""
            .CC = ""
            .BCC = ""
            .Subject = "Response Required | " & cell.Offset(0, 3).Value & " | New Opportunity"
            .HTMLBody = "Hi " & Left(cell.Offset(0, 2).Value, InStr(cell.Offset(0, 2).Value, " ") - 1) & "," & "<br><br>" & _
                        "Hope you are well! We noticed you created an opportunity at <a href='" & cell.Offset(0, 16).Value & "'>" & cell.Offset(0, 3).Value & "</a> for " & cell.Offset(0, 15).Value & " at a “" & cell.Offset(0, 6).Value & "” stage in the last 7 days. Could you confirm if this data in the system is correct?" & "<br><br>" & _
                        "If yes, please provide us with a little context (2-3 brief bullet points is all) on the nature of the work so that we may share the details with Arthur (i.e. what we are proposing to the Client, are we collaborating across any of our PS horizontals [SPEED, Practices, Partners], are we competing for the work, if so against whom?)." & "<br><br>" & _
                        "Thanks in advance for your prompt reply, if possible by EOD " & Format(cell.Value + 1, "Long Date") & " would be really appreciated."
            .Display
        End With
    End If
Next cell

Set OutMail = Nothing
Set OutApp = Nothing

End Sub

Let me know if you have any further questions!

You can modify the CC line in the code to include both email addresses as follows:

perlCopy code
.CC = "Meenakshi Katyal <[email protected]>; Amit Kataria <[email protected]>"

Here's the updated code with the modification:

mathematicaCopy code
Sub CreateEmails()

Dim OutApp As Object
Dim OutMail As Object
Dim rng As Range
Dim cell As Range
Dim dumpDate As Date

Set OutApp = CreateObject("Outlook.Application")
Set rng = Range("A2:Q" & Cells(Rows.Count, "A").End(xlUp).Row)

For Each cell In rng
    If cell.Value = Date And cell.Offset(0, 2).Value <> "" And cell.Offset(0, 3).Value <> "" Then
        Set OutMail = OutApp.CreateItem(0)
        With OutMail
            .To = ""
            .CC = "Meenakshi Katyal <[email protected]>; Amit Kataria <[email protected]>"
            .BCC = ""
            .Subject = "Response Required | " & cell.Offset(0, 3).Value & " | New Opportunity"
            .HTMLBody = "Hi " & Split(cell.Offset(0, 2).Value, " ")(0) & "," & "<br><br>" & _
                        "Hope you are well! We noticed you created an opportunity at <a href='" & cell.Offset(0, 16).Value & "'>" & cell.Offset(0, 3).Value & "</a> for " & cell.Offset(0, 15).Value & " at a “" & cell.Offset(0, 6).Value & "” stage in the last 7 days. Could you confirm if this data in the system is correct?" & "<br><br>" & _
                        "If yes, please provide us with a little context (2-3 brief bullet points is all) on the nature of the work so that we may share the details with Arthur (i.e. what we are proposing to the Client, are we collaborating across any of our PS horizontals [SPEED, Practices, Partners], are we competing for the work, if so against whom?)." & "<br><br>" & _
                        "Thanks in advance for your prompt reply, if possible by EOD " & Format(cell.Value + 1, "Long Date") & " would be really appreciated."
            .Display
        End With
    End If
Next cell

Set OutMail = Nothing
Set OutApp = Nothing

End Sub

Please note that this will send the email to both recipients in the CC field.

Great, the edits have been noted. The updated code looks good, and the CC field has been updated to include the two email addresses you provided. Is there anything else you would like to modify?

To preserve the formatting of the TCV cell, you can modify the .HTMLBody line to use the Text property of the cell instead of its Value property. This should preserve the formatting of the TCV cell in the email body.

Here's an example of how you could modify the .HTMLBody line to incorporate the Text property:

vbCopy code
.HTMLBody = "Hi " & Split(cell.Offset(0, 2).Value, " ")(0) & "," & "<br><br>" & _
            "Hope you are well! We noticed you created an opportunity at <a href='" & cell.Offset(0, 16).Value & "'>" & cell.Offset(0, 3).Value & " for " & cell.Offset(0, 15).Text & " </a> at a “" & cell.Offset(0, 6).Value & "” stage in the last 7 days. Could you confirm if this data in the system is correct?" & "<br><br>" & _
            "If yes, please provide us with a little context (2-3 brief bullet points is all) on the nature of the work so that we may share the details with Arthur (i.e. what we are proposing to the Client, are we collaborating across any of our PS horizontals [SPEED, Practices, Partners], are we competing for the work, if so against whom?)." & "<br><br>" & _
            "Thanks in advance for your prompt reply, if possible by EOD " & Format(cell.Value + 1, "Long Date") & " would be really appreciated."

In this example, the Text property of the cell.Offset(0, 15) cell is used instead of its Value property.

Sure! Here's the updated code:

mathematicaCopy code
Sub CreateEmails()
    
    Dim OutApp As Object
    Dim OutMail As Object
    Dim rng As Range
    Dim cell As Range
    Dim dumpDate As Date
    
    Set OutApp = CreateObject("Outlook.Application")
    Set rng = Range("A2:Q" & Cells(Rows.Count, "A").End(xlUp).Row)
    
    For Each cell In rng
        If cell.Value = Date And cell.Offset(0, 2).Value <> "" And cell.Offset(0, 3).Value <> "" Then
            Set OutMail = OutApp.CreateItem(0)
            With OutMail
                .To = ""
                .CC = "Meenakshi Katyal <[email protected]>; Amit Kataria <[email protected]>"
                .BCC = ""
                .Subject = "Response Required | " & cell.Offset(0, 3).Value & " | New Opportunity"
                .HTMLBody = "Hi " & Split(cell.Offset(0, 2).Value, " ")(0) & "," & "<br><br>" & _
                            "Hope you are well! We noticed you created an opportunity at <a href='" & cell.Offset(0, 16).Value & "'>" & cell.Offset(0, 3).Value & "</a> for " & Format(cell.Offset(0, 15).Value, "#,##0.00") & " at a “" & cell.Offset(0, 6).Value & "” stage in the last 7 days. Could you confirm if this data in the system is correct?" & "<br><br>" & _
                            "If yes, please provide us with a little context (2-3 brief bullet points is all) on the nature of the work so that we may share the details with Arthur (i.e. what we are proposing to the Client, are we collaborating across any of our PS horizontals [SPEED, Practices, Partners], are we competing for the work, if so against whom?)." & "<br><br>" & _
                            "Thanks in advance for your prompt reply, if possible by EOD " & Format(cell.Value + 1, "Long Date") & " would be really appreciated."
                .Display
            End With
        End If
    Next cell
    
    Set OutMail = Nothing
    Set OutApp = Nothing
    
End Sub

This will format the TCV value in the email body with a thousands separator and two decimal places.

Sure, here's the updated code:

mathematicaCopy code
Sub CreateEmails()
    
    Dim OutApp As Object
    Dim OutMail As Object
    Dim rng As Range
    Dim cell As Range
    Dim dumpDate As Date
    
    Set OutApp = CreateObject("Outlook.Application")
    Set rng = Range("A2:O" & Cells(Rows.Count, "A").End(xlUp).Row)
    
    For Each cell In rng
        If cell.Value = Date And cell.Offset(0, 2).Value <> "" And cell.Offset(0, 3).Value <> "" And cell.Offset(0, 6).Value <> "" And cell.Offset(0, 8).Value <> "" And cell.Offset(0, 14).Value <> "" And cell.Offset(0, 15).Value <> "" And cell.Offset(0, 8).Value = "Closed Won" Then
            Set OutMail = OutApp.CreateItem(0)
            With OutMail
                .To = cell.Offset(0, 2).Value
                .CC = "Meenakshi Katyal <[email protected]>; Amit Kataria <[email protected]>"
                .BCC = ""
                .Subject = "Response Required | " & cell.Offset(0, 3).Value & " | Opportunity Closed Won"
                .HTMLBody = "Hi " & cell.Offset(0, 2).Value & "," & "<br><br>" & _
                            "Hope you are well! We noticed you closed an opportunity at " & cell.Offset(0, 3).Value & " for " & Format(cell.Offset(0, 15).Value, "$#,##0.00") & ", stating that the “" & cell.Offset(0, 9).Value & "” in the last 7 days. Could you confirm if this data in the system is correct?" & "<br><br>" & _
                            "If not, please provide us with some insight into this, that we could share with Arthur for our weekly reports." & "<br><br>" & _
                            "Thanks in advance for your prompt reply, if possible by EOD " & Format(cell.Value + 1, "Long Date") & " would be really appreciated."
                .Display
            End With
        End If
    Next cell
    
    Set OutMail = Nothing
    Set OutApp = Nothing
    
End Sub

Please make sure to update the email addresses in the CC field before running the macro.

Sure, here's the updated code with the Subject line changed:

mathematicaCopy code
Sub CreateEmails()

Dim OutApp As Object
Dim OutMail As Object
Dim rng As Range
Dim cell As Range
Dim dumpDate As Date

Set OutApp = CreateObject("Outlook.Application")
Set rng = Range("A2:Q" & Cells(Rows.Count, "A").End(xlUp).Row)

For Each cell In rng
    If cell.Value = Date And cell.Offset(0, 9).Value <> "" And cell.Offset(0, 2).Value <> "" And cell.Offset(0, 3).Value <> "" And cell.Offset(0, 4).Value <> "" Then
        Set OutMail = OutApp.CreateItem(0)
        With OutMail
            .To = cell.Offset(0, 9).Value
            .CC = "Meenakshi Katyal <[email protected]>; Amit Kataria <[email protected]>"
            .BCC = ""
            .Subject = "Response Required | " & cell.Offset(0, 3).Value & " | Opportunity closed"
            .HTMLBody = "Hi " & cell.Offset(0, 2).Value & "," & "<br><br>" & _
                        "Hope you are well! We noticed you closed an opportunity at " & cell.Offset(0, 3).Value & " for " & Format(cell.Offset(0, 15).Value, "$#,##0.00") & ", stating that the """ & cell.Offset(0, 8).Value & """ in the last 7 days. Could you confirm if this data in the system is correct?" & "<br><br>" & _
                        "If not, please provide us with some insight into this, that we could share with Arthur for our weekly reports." & "<br><br>" & _
                        "Thanks in advance for your prompt reply, if possible by EOD " & Format(cell.Value + 1, "Long Date") & " would be really appreciated."
            .Display
        End With
    End If
Next cell

Set OutMail = Nothing
Set OutApp = Nothing

End Sub

Please note that the subject line will now include the account name from column D, and the TCV in the email body will be formatted as currency.

The If statement checks three conditions:

1. cell.Value = DumpDate: This condition checks whether the value in the cell is equal to the DumpDate. If the value in the cell matches the DumpDate, then the code proceeds to check the other two conditions.
2. cell.Offset(0, 2).Value <> "": This condition checks whether there is a non-empty value in the cell in the third column to the right of the cell. This column contains the name of the person responsible for the opportunity.
3. cell.Offset(0, 3).Value <> "": This condition checks whether there is a non-empty value in the cell in the fourth column to the right of the cell. This column contains the name of the account for which the opportunity was created.

If all three conditions are true, the code proceeds to create an email.

You're welcome! If you have any other questions or concerns, feel free to ask.

I apologize for the mistake. Here's the corrected code with only the first name in the name column:

mathematicaCopy code
Sub CreateEmails()

Dim OutApp As Object
Dim OutMail As Object
Dim rng As Range
Dim cell As Range
Dim dumpDate As Date

Set OutApp = CreateObject("Outlook.Application")
Set rng = Range("A2:Q" & Cells(Rows.Count, "A").End(xlUp).Row)

For Each cell In rng
    If cell.Value = Date And cell.Offset(0, 2).Value <> "" And cell.Offset(0, 3).Value <> "" And cell.Offset(0, 13).Value <> "" And cell.Offset(0, 8).Value = "Closed Won" Then
        Set OutMail = OutApp.CreateItem(0)
        With OutMail
            .To = cell.Offset(0, 13).Value
            .CC = "Meenakshi Katyal <[email protected]>; Amit Kataria <[email protected]>"
            .BCC = ""
            .Subject = "Response Required | " & cell.Offset(0, 3).Value & " | Opportunity closed"
            .HTMLBody = "Hi " & Split(cell.Offset(0, 2).Value, " ")(0) & "," & "<br><br>" & _
                        "Hope you are well! We noticed you closed an opportunity at " & cell.Offset(0, 3).Value & " for " & Format(cell.Offset(0, 14).Value, "$#,##0") & ", stating that the “" & cell.Offset(0, 8).Value & "” in the last 7 days. Could you confirm if this data in the system is correct?" & "<br><br>" & _
                        "If not, please provide us with some insight into this, that we could share with Arthur for our weekly reports." & "<br><br>" & _
                        "Thanks in advance for your prompt reply, if possible by EOD " & Format(cell.Value + 1, "Long Date") & " would be really appreciated."
            .Display
        End With
    End If
Next cell

Set OutMail = Nothing
Set OutApp = Nothing

End Sub

I hope this helps!

Sure, here's the updated code with the changes you requested:

mathematicaCopy code
Sub CreateEmails()

Dim OutApp As Object
Dim OutMail As Object
Dim rng As Range
Dim cell As Range
Dim dumpDate As Date

Set OutApp = CreateObject("Outlook.Application")
Set rng = Range("A2:Q" & Cells(Rows.Count, "A").End(xlUp).Row)

For Each cell In rng
    If cell.Value = Date And cell.Offset(0, 2).Value <> "" And cell.Offset(0, 3).Value <> "" And cell.Offset(0, 15).Value <> "" And cell.Offset(0, 8).Value = "Closed" Then
        Set OutMail = OutApp.CreateItem(0)
        With OutMail
            .To = cell.Offset(0, 2).Value
            .CC = "Meenakshi Katyal <[email protected]>; Amit Kataria <[email protected]>"
            .BCC = ""
            .Subject = "Response Required | " & cell.Offset(0, 3).Value & " | Opportunity closed"
            .HTMLBody = "Hi " & Split(cell.Offset(0, 2).Value, " ")(0) & "," & "<br><br>" & _
                        "Hope you are well! We noticed you closed an opportunity at <a href='" & cell.Offset(0, 16).Value & "'>" & cell.Offset(0, 3).Value & "</a> for " & cell.Offset(0, 14).Text & ", stating that the “" & cell.Offset(0, 8).Value & "” in the last 7 days. Could you confirm if this data in the system is correct?" & "<br><br>" & _
                        "If not, please provide us with some insight into this, that we could share with Arthur for our weekly reports." & "<br><br>" & _
                        "Thanks in advance for your prompt reply, if possible by EOD " & Format(cell.Value + 1, "Long Date") & " would be really appreciated."
            .Display
        End With
    End If
Next cell

Set OutMail = Nothing
Set OutApp = Nothing

End Sub

I hope this helps!

There seems to be a typo in the updated code. In the line .HTMLBody = "Hi " & name & "," & "<br><br>" & _, you used name instead of firstName.

Here's the updated code with that fix:

mathematicaCopy code
Sub CreateEmails()
    
    Dim OutApp As Object
    Dim OutMail As Object
    Dim rng As Range
    Dim cell As Range
    Dim dumpDate As Date
    Dim firstName As String
    
    Set OutApp = CreateObject("Outlook.Application")
    Set rng = Range("A2:Q" & Cells(Rows.Count, "A").End(xlUp).Row)
    
    For Each cell In rng
        If IsDate(cell.Value) And cell.Value = Date And cell.Offset(0, 2).Value <> "" And cell.Offset(0, 3).Value <> "" Then
            firstName = Split(cell.Offset(0, 2).Value, " ")(0)
            Set OutMail = OutApp.CreateItem(0)
            With OutMail
                .To = cell.Offset(0, 3).Value
                .CC = "Meenakshi Katyal <[email protected]>; Amit Kataria <[email protected]>"
                .BCC = ""
                .Subject = "Response Required | " & cell.Offset(0, 3).Value & " | Opportunity closed"
                .HTMLBody = "Hi " & firstName & "," & "<br><br>" & _
                            "Hope you are well! We noticed you closed an opportunity at " & "<a href='" & cell.Offset(0, 16).Value & "'>" & cell.Offset(0, 3).Value & "</a> for " & cell.Offset(0, 14).Value & ", stating that the “" & cell.Offset(0, 8).Value & "” in the last 7 days. Could you confirm if this data in the system is correct?" & "<br><br>" & _
                            "If not, please provide us with some insight into this, that we could share with Arthur for our weekly reports." & "<br><br>" & _
                            "Thanks in advance for your prompt reply, if possible by EOD " & Format(cell.Value + 1, "Long Date") & " would be really appreciated."
                .Display
            End With
        End If
    Next cell
    
    Set OutMail = Nothing
    Set OutApp = Nothing
    
End Sub

Please give it a try and let me know if it works for you.

Yes, you can add a pop-up message with a text and an action button "OK" before the email drafting processes in this macro using the MsgBox function in VBA.

Here's an example of how you can add a pop-up message before the email drafting:

mathematicaCopy code
Sub CreateEmails()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim rng As Range
    Dim cell As Range
    Dim dumpDate As Date
    Dim response As VbMsgBoxResult ' define response variable

    response = MsgBox("Are you sure you want to create emails?", vbOKCancel, "Confirmation") ' display pop-up message and wait for user response

    If response = vbCancel Then ' check user response
        Exit Sub ' exit macro if user clicked "Cancel"
    End If

    Set OutApp = CreateObject("Outlook.Application")
    Set rng = Range("A2:Q" & Cells(Rows.Count, "A").End(xlUp).Row)

    For Each cell In rng
        If cell.Value = Date And cell.Offset(0, 2).Value <> "" And cell.Offset(0, 3).Value <> "" And cell.Offset(0, 15).Value <> "" And cell.Offset(0, 16).Value <> "" And cell.Offset(0, 7).Value = "Closed" Then
            Set OutMail = OutApp.CreateItem(0)
            With OutMail
                .To = cell.Offset(0, 3).Value
                .CC = "Meenakshi Katyal <[email protected]>; Amit Kataria <[email protected]>"
                .BCC = ""
                .Subject = "Response Required | " & cell.Offset(0, 3).Value & " | Opportunity closed"
                .HTMLBody = "Hi " & Split(cell.Offset(0, 2).Value, " ")(0) & "," & "<br><br>" & _
                            "Hope you are well! We noticed you closed an opportunity at <a href='" & cell.Offset(0, 16).Value & "'>" & cell.Offset(0, 3).Value & "</a> for " & FormatCurrency(cell.Offset(0, 14).Value, 2) & ", stating that the “" & cell.Offset(0, 8).Value & "” in the last 7 days. Could you confirm if this data in the system is correct?" & "<br><br>" & _
                            "If not, please provide us with some insight into this, that we could share with Arthur for our weekly reports." & "<br><br>" & _
                            "Thanks in advance for your prompt reply, if possible by EOD " & Format(cell.Value + 1, "Long Date") & " would be really appreciated."
                .Display
            End With
        End If
    Next cell

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

In the above code, a pop-up message with the text "Are you sure you want to create emails?" and an action button "OK" and "Cancel" will be displayed before the email drafting process. If the user clicks the "OK" button, the macro will continue and draft emails. If the user clicks the "Cancel" button, the macro will exit without drafting emails.

Sure, here's how you can add a pop-up message with the text and action button "OK" before the email drafting process in your macro:

vbnetCopy code
Sub CreateEmails()

Dim OutApp As Object
Dim

Sure, here is the updated code that includes the pop-up message:

mathematicaCopy code
Sub CreateEmails()

Dim OutApp As Object
Dim OutMail As Object
Dim rng As Range
Dim cell As Range
Dim dumpDate As Date
Dim response As Integer

response = MsgBox("Quickly check these points before proceeding:" & vbNewLine & "1. Did you add the opportunity links to all the new rows?" & vbNewLine & "2. Did you add today's date to the first column 'Dump Date'?" & vbNewLine & "3. Ensure that the TCV column is in the right format. Copy the format from the rows above, if needed." & vbNewLine & vbNewLine & "Click OK to create the emails in draft. You have to add the email id of the receiver, proofread the email content and then click send.", vbOKCancel, "Create Emails")

If response = vbCancel Then
    Exit Sub
End If

Set OutApp = CreateObject("Outlook.Application")
Set rng = Range("A2:Q" & Cells(Rows.Count, "A").End(xlUp).Row)

For Each cell In rng
    If cell.Value = Date And cell.Offset(0, 2).Value <> "" And cell.Offset(0, 3).Value <> "" Then
        Set OutMail = OutApp.CreateItem(0)
        With OutMail
            .To = ""
            .CC = "Meenakshi Katyal <[email protected]>; Amit Kataria <[email protected]>"
            .BCC = ""
            .Subject = "Response Required | " & cell.Offset(0, 3).Value & " | Opportunity closed"
            .HTMLBody = "Hi " & Split(cell.Offset(0, 2).Value, " ")(0) & "," & "<br><br>" & _
                        "Hope you are well! We noticed you closed an opportunity at <a href='" & cell.Offset(0, 16).Value & "'>Opportunity</a> for " & Format(cell.Offset(0, 14).Value, "#,##0") & ", stating that the “" & cell.Offset(0, 8).Value & "” in the last 7 days. Could you confirm if this data in the system is correct?" & "<br><br>" & _
                        "If not, please provide us with some insight into this, that we could share with Arthur for our weekly reports." & "<br><br>" & _
                        "Thanks in advance for your prompt reply, if possible by EOD Thursday, April 20th would be really appreciated."
            .Display
        End With
    End If
Next cell

Set OutMail = Nothing
Set OutApp = Nothing

End Sub

Please let me know if you have any further questions or concerns.