Bhubon Costa, Author at ExcelDemy

User Posts: Bhubon Costa

How to Add Notes in Excel Formula (4 Effective Ways)

You can use notes or comments in a cell to help you better understand a formula and specify its significance. There are a few different ways to add notes or ...

Bhubon Costa Jan 2, 2023

READ MORE +

How to Make a Relative Frequency Table in Excel (with Easy Steps)

We frequently need to determine how vital and influential a specific range is. The relative frequency of a data set can be used to measure the effect of a ...

Bhubon Costa Nov 22, 2022

READ MORE +

How to Populate an Excel Spreadsheet from a Web Form

When we conduct surveys and must export the data into a spreadsheet, we have a lot of work to do. Let’s say you have to make entries for a survey of about 1 ...

Bhubon Costa Feb 26, 2023

READ MORE +

How to Update a Pivot Table Automatically When Source Data Changes

When you need to refresh or update data in an existing PivotTable after creating it, you must use the Change Data Source command. However, manually updating ...

Bhubon Costa Feb 12, 2023

READ MORE +

Excel VBA: Save Workbook without a Prompt (with Easy Steps)

When saving a Macro file in the exact location as before, it always prompts you to replace the file with an alert message. It can be inconvenient when you have ...

Bhubon Costa Feb 27, 2023 2

READ MORE +

How to Calculate Projected Sales in Excel (2 Easy Ways)

In statistical analysis, we frequently need to predict results in order to plan ahead of time. Excel has some analytical functions and features for doing ...

Bhubon Costa Apr 11, 2023

READ MORE +

How to Add Symbol Before a Number in Excel (3 Ways)

Excel has a multitude of symbols to choose from. When combined with a number, special characters or symbols have different meanings. In most cases, you won't ...

Bhubon Costa Nov 21, 2022

READ MORE +

How to Calculate Standard Error of Regression in Excel (with Easy Steps)

We use regression analysis when we have data from two variables from two different sources and want to build a relationship between them. Regression analysis ...

Bhubon Costa Sep 28, 2022

READ MORE +

How to Download Historical Stock Data into Excel (with Easy Steps)

You'll need historical stock data from companies all over the world to perform financial stock analysis. With the blessings of Excel, you can easily download ...

Bhubon Costa Feb 19, 2023 2

READ MORE +

How to Calculate Covariance Matrix in Excel (with Easy Steps)

In statistical analysis, covariance is an analysis of the relationship between changes in one variable and changes in another. It's a metric for determining ...

Bhubon Costa Jun 10, 2022

READ MORE +

How to Stop Excel from Rounding Up Decimals (4 Easy Methods)

We frequently round Excel numbers to make them easier to understand. However, when working with sensitive data, it can be inaccurate. Simple fractional changes ...

Bhubon Costa Dec 29, 2022

READ MORE +

Send Reminder Email Automatically from an Excel Worksheet Using VBA

In the banking or financial system, you might have to deal with a lot of clients. So, you have to deal with lots of information. That’s why you might need to ...

Bhubon Costa Aug 31, 2022 39

READ MORE +

Connect Slicer to Multiple Pivot Tables from Different Data Source

In PivotTable, the Slicer is used to construct a link between fields that are comparable. It's a fantastic tool for comparing and analyzing data. In this ...

Bhubon Costa May 7, 2023

READ MORE +

[Solved]: All Excel Files Opening as Read Only (6 Solutions)

Occasionally, when opening an Excel file, an alert message appears informing you that the file is read-only. When you see the message several times, you may ...

Bhubon Costa Apr 14, 2023

READ MORE +

How to Send Email from Excel List (2 Effective Ways)

When you need to send a mass email to a large group of people, you'll need an automated process that can handle repetitive tasks quickly. Creating an Excel ...

Bhubon Costa Jul 21, 2022

READ MORE +

Browsing All Comments By: Bhubon Costa

Bhubon Costa May 8, 2023 at 4:45 PM

Dear Nouran,

I am delighted to hear that your previous queries have been resolved. It seems like you have invested a lot of patience and effort, considering you had to work with 18 variables to accomplish your task!

Regarding your further queries, you want to send reminder emails multiple times with different time intervals. For instance, you want to send the first mail three days prior to the deadline, the second mail five days prior to the deadline, and the final mail seven days before the deadline. To accomplish this, we have utilized the following dataset, and as of today, it is the 8th of May, 2023.

Now, you have to modify the If section of the codes as in the image below.

Here, is the full updated code.

Public Sub SendReminderMail_Multiple()
'Declare Variables
    Dim XDueDate As Range
    Dim XRcptsEmail As Range
    Dim xMailContent As Range
    Dim xRngDn As Range
    Dim xCrtOut As Object
    Dim xValDateRng As String
    Dim xValSendRng As String
    Dim k As Long
    Dim xMailSections As Object
    Dim xFinalRw As Long
    Dim CrVbLf As String
    Dim xMsg As String
    Dim xSubEmail As String
    On Error Resume Next
    'To select the date column insert a input box
    Set XDueDate = Application.InputBox("Select the column for Deadline/Due Date date column:", "ExcelDemy", , , , , , 8)
    If XDueDate Is Nothing Then Exit Sub
    'Insert a input box for selecting the recipients
    Set XRcptsEmail = Application.InputBox("Choose the column for the email addresses of the recipients:", "ExcelDemy", , , , , , 8)
    If XRcptsEmail Is Nothing Then Exit Sub
    'To enter the text mail, insert a input box
    Set xMailContent = Application.InputBox("In your email, choose the column with the reminded text:", "ExcelDemy", , , , , , 8)
    If xMailContent Is Nothing Then Exit Sub
    'Count rows for the due dates
    xFinalRw = XDueDate.Rows.Count
    Set XDueDate = XDueDate(1)
    Set XRcptsEmail = XRcptsEmail(1)
    Set xMailContent = xMailContent(1)
    'Set command to open MS Outlook Application
    Set xCrtOut = CreateObject("Outlook.Application")
    
    'Apply For loop to conduct the operation in each row one by one
    For k = 1 To xFinalRw
        xValDateRng = ""
        xValDateRng = XDueDate.Offset(k - 1).Value
        'Apply If condition for the Due Date values
        
        If xValDateRng <> "" Then
            Dim xDaysDiff As Integer
            xDaysDiff = DateDiff("d", Date, CDate(xValDateRng))
            If xDaysDiff = 3 Or xDaysDiff = 5 Or xDaysDiff = 7 Then
                xValSendRng = XRcptsEmail.Offset(k - 1).Value
                
                'Create the subject, body and text contents with the required variables
                xSubEmail = xMailContent.Offset(k - 1).Value & " on " & xValDateRng
                CrVbLf = "<br><br>"
                xMsg = "<HTML><BODY>"
                xMsg = xMsg & "Dear " & xValSendRng & CrVbLf
                xMsg = xMsg & "Text : " & xMailContent.Offset(k - 1).Value & CrVbLf
                xMsg = xMsg & "</BODY></HTML>"
            'Create the email
            Set xMailSections = xCrtOut.CreateItem(0)
            'Define the position to place the Subject, Body and Recipients Address
            With xMailSections
                .Subject = xSubEmail
                .To = xValSendRng
                .HTMLBody = xMsg
                .Display
                
                '.Send
            
            End With
            Set xMailSections = Nothing
        End If
    End If
    Next
    Set xCrtOut = Nothing
End Sub

I hope that this response has resolved your current queries. If you would like to automate the program, please refer to our previous replies for detailed instructions. Additionally, please inform us if you have any further questions.

Regards,
ExcelDemy Team

Bhubon Costa May 8, 2023 at 4:42 PM

Dear Nouran,

Now, you have to modify the If section of the codes as in the image below.

Here, is the full updated code.

Public Sub SendReminderMail_Multiple()
'Declare Variables
    Dim XDueDate As Range
    Dim XRcptsEmail As Range
    Dim xMailContent As Range
    Dim xRngDn As Range
    Dim xCrtOut As Object
    Dim xValDateRng As String
    Dim xValSendRng As String
    Dim k As Long
    Dim xMailSections As Object
    Dim xFinalRw As Long
    Dim CrVbLf As String
    Dim xMsg As String
    Dim xSubEmail As String
    On Error Resume Next
    'To select the date column insert a input box
    Set XDueDate = Application.InputBox("Select the column for Deadline/Due Date date column:", "ExcelDemy", , , , , , 8)
    If XDueDate Is Nothing Then Exit Sub
    'Insert a input box for selecting the recipients
    Set XRcptsEmail = Application.InputBox("Choose the column for the email addresses of the recipients:", "ExcelDemy", , , , , , 8)
    If XRcptsEmail Is Nothing Then Exit Sub
    'To enter the text mail, insert a input box
    Set xMailContent = Application.InputBox("In your email, choose the column with the reminded text:", "ExcelDemy", , , , , , 8)
    If xMailContent Is Nothing Then Exit Sub
    'Count rows for the due dates
    xFinalRw = XDueDate.Rows.Count
    Set XDueDate = XDueDate(1)
    Set XRcptsEmail = XRcptsEmail(1)
    Set xMailContent = xMailContent(1)
    'Set command to open MS Outlook Application
    Set xCrtOut = CreateObject("Outlook.Application")
    
    'Apply For loop to conduct the operation in each row one by one
    For k = 1 To xFinalRw
        xValDateRng = ""
        xValDateRng = XDueDate.Offset(k - 1).Value
        'Apply If condition for the Due Date values
        
        If xValDateRng <> "" Then
            Dim xDaysDiff As Integer
            xDaysDiff = DateDiff("d", Date, CDate(xValDateRng))
            If xDaysDiff = 3 Or xDaysDiff = 5 Or xDaysDiff = 7 Then
                xValSendRng = XRcptsEmail.Offset(k - 1).Value
                
                'Create the subject, body and text contents with the required variables
                xSubEmail = xMailContent.Offset(k - 1).Value & " on " & xValDateRng
                CrVbLf = "<br><br>"
                xMsg = "<HTML><BODY>"
                xMsg = xMsg & "Dear " & xValSendRng & CrVbLf
                xMsg = xMsg & "Text : " & xMailContent.Offset(k - 1).Value & CrVbLf
                xMsg = xMsg & "</BODY></HTML>"
            'Create the email
            Set xMailSections = xCrtOut.CreateItem(0)
            'Define the position to place the Subject, Body and Recipients Address
            With xMailSections
                .Subject = xSubEmail
                .To = xValSendRng
                .HTMLBody = xMsg
                .Display
                
                '.Send
            
            End With
            Set xMailSections = Nothing
        End If
    End If
    Next
    Set xCrtOut = Nothing
End Sub

Regards,
ExcelDemy Team

Reply
Bhubon Costa May 3, 2023 at 4:16 PM
Yes, you can still run the program when your Excel App is closed. You do not need to run the program every day manually. By doing so, whenever you open your PC your program will be executed automatically. Follow the steps below to do so.
Step 1:

1. From the Start Menu type Run to open the Windows Run dialog box and paste the following path:

%AppData%\Microsoft\Windows\Start Menu\Programs\Startup

2. Click OK.

Step 2: Place your Excel File in the Startup folder.

Step 3:

1. Open your Excel file Macro Window.

2. In the Project Explorer, double-click the ThisWorkbook object to open the code window for the workbook.

3. In the code window, select Workbook from the drop-down list at the top.

4. Then, select the Workbook_Open event from the drop-down list on the right.

Step 4:

1. Type or paste the VBA code that you want to run when the file is opened between the Private Sub Workbook_Open() and End Sub lines.

2. Save the Excel file.
```
Private Sub Workbook_Open()
    "Your Full VBA Codes"
End Sub
```
Now, whenever you open your PC, the VBA code you wrote will be automatically executed. Hope this will resolve your issue. Please let us know your further feedback, our ExcelDemy Team is always relentless to welcome your issues.

Regards,
ExcelDemy Team

Bhubon Costa May 2, 2023 at 4:23 PM

Answer 1: Let’s say you want to add one more column for the mail content portion. So, we have assigned VBA codes with one more variable xMailContent2

Copy the codes below and apply them to your Module.

Public Sub SendReminderMail()
'Declare the variables
    Dim XDueDate As Range
    Dim XRcptsEmail As Range
    Dim xMailContent As Range
    Dim xMailContent2 As Range
    
    Dim xRngDn As Range
    Dim xCrtOut As Object
    Dim xValDateRng As String
    Dim xValSendRng As String
    Dim k As Long
    Dim xMailSections As Object
    Dim xFinalRw As Long
    Dim CrVbLf As String
    Dim xMsg As String
    Dim xSubEmail As String
    On Error Resume Next
    
    'To select the date column insert a input box
    Set XDueDate = Application.InputBox("Select the column for Deadline/Due Date date column:", "ExcelDemy", , , , , , 8)
    If XDueDate Is Nothing Then Exit Sub
    
    'Insert a input box for selecting the recipients
    Set XRcptsEmail = Application.InputBox("Choose the column for the email addresses of the recipients:", "ExcelDemy", , , , , , 8)
    If XRcptsEmail Is Nothing Then Exit Sub
    
    'To enter the text mail content 1, insert a input box
    Set xMailContent = Application.InputBox("In your email, choose the column with the reminded text:", "ExcelDemy", , , , , , 8)
    If xMailContent Is Nothing Then Exit Sub
    
    'To enter the text mail content 2, insert a input box
    Set xMailContent2 = Application.InputBox("In your email, choose the column with the reminded text:", "ExcelDemy", , , , , , 8)
    If xMailContent2 Is Nothing Then Exit Sub
    
    'Count rows for the due dates
    xFinalRw = XDueDate.Rows.Count
    Set XDueDate = XDueDate(1)
    Set XRcptsEmail = XRcptsEmail(1)
    Set xMailContent = xMailContent(1)
    Set xMailContent2 = xMailContent2(1)
    'Set command to open MS Outlook Application
    Set xCrtOut = CreateObject("Outlook.Application")
    
    'Apply For loop to conduct the operation in each row one by one
    For k = 1 To xFinalRw
        xValDateRng = ""
        xValDateRng = XDueDate.Offset(k - 1).Value
        'Apply If condition for the Due Date values
        If xValDateRng <> "" Then
        'Condition set to send mail if the difference between due dates and current date is greater than 1 and less than 7 days
        'Means 1 < X< 7, X = Due Date - Current Date
        If CDate(xValDateRng) - Date <= 7 And CDate(xValDateRng) - Date > 0 Then
            xValSendRng = XRcptsEmail.Offset(k - 1).Value
            'Create the subject, body and text contents with the required variables
            xSubEmail = xMailContent.Offset(k - 1).Value & " on " & xValDateRng
            CrVbLf = "<br><br>"
            xMsg = "<HTML><BODY>"
            xMsg = xMsg & "Dear " & xValSendRng & CrVbLf
            xMsg = xMsg & "Text : " & xMailContent.Offset(k - 1).Value & CrVbLf
            xMsg = xMsg & "Text : " & xMailContent2.Offset(k - 1).Value & CrVbLf
            xMsg = xMsg & "</BODY></HTML>"
            'Create the email
            Set xMailSections = xCrtOut.CreateItem(0)
            'Define the position to place the Subject, Body and Recipients Address
            With xMailSections
                .Subject = xSubEmail
                .To = xValSendRng
                .HTMLBody = xMsg
                .Display
                
                '.Send
            
            End With
            Set xMailSections = Nothing
        End If
    End If
    Next
    Set xCrtOut = Nothing
End Sub

See the output as images below:

Answer 2: Unfortunately, you cannot run the program without opening the Excel App. You may get this done by applying API. However, you can try the below process but it has some troubleshooting issues. Please let us know if you get solved your issue.

Open Excel and create a new workbook or open an existing one.
Press Alt + F11 to open the Visual Basic Editor.
Write your VBA code in a module.
Save the workbook as a macro-enabled workbook (*.xlsm) format.
Close Excel.
Open the Task Scheduler by typing “Task Scheduler” in the Start menu search bar and pressing Enter.
Click on “Create Task” in the right-hand panel.
In the “General” tab, give the task a name and a description.
In the “Actions” tab, click “New” and select “Start a program” as the action.
In the “Program/script” field, browse to the location where your Excel workbook is saved and select it.
In the “Add arguments” field, enter the full path and filename of the workbook you want to run, enclosed in quotes.
Click “OK” to close the “New Action” dialog box.
In the “Triggers” tab, click “New” and select the frequency and timing for the task to run.
Click “OK” to close the “New Trigger” dialog box.
Click “OK” to create the task.

On the other hand, you can automatically run when you open the Excel file, you do not need to manually run the code. Simply, apply the VBA codes inside the Private Sub event.

Private Sub Workbook_Open()
    "Your Full VBA Codes"
End Sub

Reply
Bhubon Costa May 3, 2023 at 4:12 PM
You do not need to run the program every day manually. You can automatically run the program whenever you open your PC. Follow the steps below to do so.
Step 1:

1. From the Start Menu type Run to open the Windows Run dialog box and paste the following path:

%AppData%\Microsoft\Windows\Start Menu\Programs\Startup

2. Click OK.

Step 2: Place your Excel File in the Startup folder.

Step 3:

1. Open your Excel file Macro Window.

2. In the Project Explorer, double-click the ThisWorkbook object to open the code window for the workbook.

3. In the code window, select Workbook from the drop-down list at the top.

4. Then, select the Workbook_Open event from the drop-down list on the right.

Step 4:

1. Type or paste the VBA code that you want to run when the file is opened between the Private Sub Workbook_Open() and End Sub lines.

2. Save the Excel file.
```
Private Sub Workbook_Open()
    "Your Full VBA Codes"
End Sub
```
Now, whenever you open your PC, the VBA code you wrote will be automatically executed. Hope this will resolve your issue. Please let us know your further feedback, our ExcelDemy Team is always relentless to welcome your issues.

Regards,
ExcelDemy Team
Reply
Bhubon Costa Mar 16, 2023 at 3:50 PM
Yeah, YUKI, you are right. Your workbook need to be opened all the time to continue running this program. But, Problem arises when you turn off your pc the program will not run. So, you need to reopen the Excel file and run the program on daily basis.

However, you can just open the Excel App. from your PC. Then it will run automatically. To do so, please insert your whole code in between Private Sub as below:
```
Private Sub auto_open()
"Your Codes"
End Sub
```
So, whenever you open the Excel App. the program will continue to run.

On the other hand, if you want to run the program just after whenever you open PC. We need some time to make it for you. You may share your Excel file with us in [email protected]. Our dedicated ExcelDemy Team is trying to resolve your issue.

Regards,
ExcelDemy Team

Bhubon Costa May 3, 2023 at 2:20 PM

Answer 1: To add one or more columns for the mail content section, you must add variables. Let’s say you want to add one more column so we will add VBA codes with one more variable xMailContent2. So, your data set may look like the following image, one column with the task name, and another with the task details.
So, apply the following VBA codes to perform your requirements.

Public Sub SendReminderMail()
'Declare the variables
    Dim XDueDate As Range
    Dim XRcptsEmail As Range
    Dim xMailContent As Range
    Dim xMailContent2 As Range
    
    Dim xRngDn As Range
    Dim xCrtOut As Object
    Dim xValDateRng As String
    Dim xValSendRng As String
    Dim k As Long
    Dim xMailSections As Object
    Dim xFinalRw As Long
    Dim CrVbLf As String
    Dim xMsg As String
    Dim xSubEmail As String
    On Error Resume Next
    
    'To select the date column insert a input box
    Set XDueDate = Application.InputBox("Select the column for Deadline/Due Date date column:", "ExcelDemy", , , , , , 8)
    If XDueDate Is Nothing Then Exit Sub
    
    'Insert a input box for selecting the recipients
    Set XRcptsEmail = Application.InputBox("Choose the column for the email addresses of the recipients:", "ExcelDemy", , , , , , 8)
    If XRcptsEmail Is Nothing Then Exit Sub
    
    'To enter the task Name, insert a input box
    Set xMailContent = Application.InputBox("In your email, choose the column with the Task Name:", "ExcelDemy", , , , , , 8)
    If xMailContent Is Nothing Then Exit Sub
    
    'To enter the task details, insert a input box
    Set xMailContent2 = Application.InputBox("In your email, choose the column with the Task Details:", "ExcelDemy", , , , , , 8)
    If xMailContent2 Is Nothing Then Exit Sub
    
    'Count rows for the due dates
    xFinalRw = XDueDate.Rows.Count
    Set XDueDate = XDueDate(1)
    Set XRcptsEmail = XRcptsEmail(1)
    Set xMailContent = xMailContent(1)
    Set xMailContent2 = xMailContent2(1)
    'Set command to open MS Outlook Application
    Set xCrtOut = CreateObject("Outlook.Application")
    
    'Apply For loop to conduct the operation in each row one by one
    For k = 1 To xFinalRw
        xValDateRng = ""
        xValDateRng = XDueDate.Offset(k - 1).Value
        'Apply If condition for the Due Date values
        If xValDateRng <> "" Then
        'Condition set to send mail if the difference between due dates and current date is greater than 1 and less than 7 days
        'Means 1 < X< 7, X = Due Date - Current Date
        If CDate(xValDateRng) - Date <= 7 And CDate(xValDateRng) - Date > 0 Then
            xValSendRng = XRcptsEmail.Offset(k - 1).Value
            'Create the subject, body and text contents with the required variables
            xSubEmail = xMailContent.Offset(k - 1).Value & " on " & xValDateRng
            CrVbLf = "<br><br>"
            xMsg = "<HTML><BODY>"
            xMsg = xMsg & "Dear " & xValSendRng & CrVbLf
            xMsg = xMsg & "Text : " & xMailContent.Offset(k - 1).Value & CrVbLf
            xMsg = xMsg & "Text : " & xMailContent2.Offset(k - 1).Value & CrVbLf
            xMsg = xMsg & "</BODY></HTML>"
            'Create the email
            Set xMailSections = xCrtOut.CreateItem(0)
            'Define the position to place the Subject, Body and Recipients Address
            With xMailSections
                .Subject = xSubEmail
                .To = xValSendRng
                .HTMLBody = xMsg
                .Display
                
                '.Send
            
            End With
            Set xMailSections = Nothing
        End If
    End If
    Next
    Set xCrtOut = Nothing
End Sub

Answer 2: You cannot run a file automatically from a Team Chanel server. However, you can automatically run the program whenever you open your PC. Follow the steps below to do so.
Step 1:

1. From the Start Menu type Run to open the Windows Run dialog box and paste the following path:

%AppData%\Microsoft\Windows\Start Menu\Programs\Startup

2. Click OK.

Step 2: Place your Excel File in the Startup folder.

Step 3:

1. Open your Excel file Macro Window.

2. In the Project Explorer, double-click the ThisWorkbook object to open the code window for the workbook.

3. In the code window, select Workbook from the drop-down list at the top.

4. Then, select the Workbook_Open event from the drop-down list on the right.

Step 4:

1. Type or paste the VBA code that you want to run when the file is opened between the Private Sub Workbook_Open() and End Sub lines.

2. Save the Excel file.

Private Sub Workbook_Open()
    "Your Full VBA Codes"
End Sub

Now, whenever you open your PC, the VBA code you wrote will be automatically executed. Hope this will resolve your issue. Please let us know your further feedback, our ExcelDemy Team is always relentless to welcome your issues.

Regards,
ExcelDemy Team

Reply
Bhubon Costa Mar 16, 2023 at 11:59 AM

Greeting Yuki,
Thank you for your queries,

This program will have to run just one time and when it will reach the due date it will send reminder emails automatically.

But you can also customize it to send it manually. Please go through the “Notes” section in Step 3 of the article.

You can stop sending emails automatically just by disabling the .Send command from the full code. It will pops up for a confirmation before sending. Moreover, you can customize the recipients names. That’s the elegance of our objective!

However, if this doesn’t solve your queries, you can mail us your Excel file with detailed instructions to: [email protected], and we’ll try to solve it as soon as possible.

Regards,
ExcelDemy Team

Bhubon Costa Mar 12, 2023 at 11:24 AM

Greetings 3ADDOULA MA5ASSAK, thank you for your question. I hope the following codes will solve your issue.

 Sub Chemical_Formula()
    Dim inputString As String
    Dim i As Integer
    Dim Char As String
    Dim Text As String
    Dim Number As String
    Dim result As String
    
    inputString = InputBox("Enter the chemical formula:")
    result = ""
    Text = ""
    Number = ""
    
    For i = 1 To Len(inputString)
        Char = Mid(inputString, i, 1)
        If IsNumeric(Char) Then
            Number = Number & Char
        Else
            If Number <> "" Then
                result = result & Text & "=" & Number & ","
                Number = ""
            End If
            If Char Like "[A-Za-z]" Then
                Text = Char
            End If
        End If
    Next i

    If Number <> "" Then
        result = result & Text & "=" & Number
    End If

    MsgBox result
End Sub

However, if this doesn’t solve your problem, you can mail us your Excel file with detailed instructions to: [email protected], and we’ll try to solve it as soon as possible.

Reply
Bhubon Costa Nov 26, 2022 at 10:41 PM

Greetings INSTASAVETUBE,
Countless thanks for your kind words of praise. The Exceldemy team has always been there to assist and has been working to make knowledge accessible to everyone globally. And without a doubt, you are the kind of great people that empower us.

Best Regards,
Bhubon Costa
(Exceldemy Team)
Reply
Bhubon Costa Aug 31, 2022 at 12:42 AM

Greetings Lalit,
Pleased to hear that your previous query has been resolved.

We have created an Excel file for you to add different subjects. Just download the file and run the code of Module 1.

https://www.exceldemy.com/wp-content/uploads/2022/08/Send_Email_Reminder_Lalit.xlsm

1. You will get an input box to select the subject column as shown in the image below.

2. Therefore, you will see the emails on display before sending them, with the selected different subjects.

Please feel free to ask more queries. Our ExcelDemy Team will reach you as soon as possible.
Reply
Bhubon Costa Aug 24, 2022 at 2:45 PM
Greetings Lalit,
You just have to add two lines in between the xMailSections portion:
Steps:
1. Add the file path of your attachment with the attachment name.
2. Apply add attachment command to add the attachment.
```
Attached_File = "C:\Users\USER\Downloads\Send_Email_Reminder.xlsm"
.Attachments.Add Attached_File
```
Please, see the image below for a detailed understanding.

After running the code, you will see results with the attachment available.

Moreover, we have a dedicated article based on your requirement. Please, go through the article to learn in detail.
Hope, this will meet your requirement. Please, give us feedback if any furthur query needed. The ExcelDemy team is always in your assistance.
Reply
Bhubon Costa Aug 22, 2022 at 10:55 AM

Greetings Emma,
Let’s us know some info at first.
1. Can you access the other methods to paste values only?
2. Can you replace data in a non-blank cell?
We have gone through every possible solution but couldn’t match the issue exactly. If you kindly share your excel file with us, we will send it back with the solution.
Email: [email protected]
Our dedicated ExcelDemy Team is always there to help.
Reply
Bhubon Costa Aug 12, 2022 at 2:19 AM
Greetings DANA,
To update the result after each entry you make, you just need to edit a one-line VBA code. It will update the table range each time you make a new entry in column B.

The VBA Code:
```
Set TableRng = Range(Range("B5"), Range("B5").End(xlDown))
```
Now, make new entries and run the program to update the result.
Hope this will work for you. Please give us feedback if you have any further queries.
Reply
Bhubon Costa Aug 11, 2022 at 2:11 PM

Thanks a lot for your appreciation. You can also put your problems here, the ExcelDemy team is always in your assistance.
Reply
Bhubon Costa Aug 11, 2022 at 12:44 AM

Thank you MOE, for your generous appreciation!
Reply
Bhubon Costa Aug 10, 2022 at 2:00 AM
Greetings Justin,
#VALUE error occurs when the function doesn’t find any recognized source value. It might have occurred if the degree values were not in degree formats. Follow the steps below to convert the value into degree format first and then apply the function Convert_to_Decimal with our VBA.

Step 1:
Add a degree (°) symbol after a number with a keyboard shortcut.
```
Alt + 0176
```
(Example: 32°).

Step 2:
Add a minute (‘) symbol after the minute’s value with the apostrophe (‘) key from the keyboard (Example: 16’).

Step 3:
Add a second (“) symbol after the second’s value by pressing
```
SHIFT + Apostrophe (')
```
(Example: 48″)

Step 4:
Your degree format will show like (32°16’48”).
Finally, apply the Convert_to_Decimal function to the degree value.

Hope this will work with your issue. You can also mail us the Excel file. We will provide the Excel file with the required solution. Please give us your further feedback.
Reply
Bhubon Costa Aug 10, 2022 at 1:10 AM

Greetings David,
Thanks for your valuable suggestion.
Reply
Bhubon Costa Aug 8, 2022 at 12:56 AM
Greetings Nishant,
To get emails one day prior the deadline date, try the following codes below.

We just edited the code in the If condition by replacing 1 with 0.

Previous code:
```
If CDate(xValDateRng) - Date <= 7 And CDate(xValDateRng) - Date > 0 Then
            xValSendRng = XRcptsEmail.Offset(k - 1).Value
```
Modified Code:
```
If CDate(xValDateRng) - Date <= 7 And CDate(xValDateRng) - Date > 1 Then
            xValSendRng = XRcptsEmail.Offset(k - 1).Value
```
Please, give us feedback, if any further query needed. The ExcelDemy team is always in your assistance.
Reply
Bhubon Costa Aug 7, 2022 at 2:45 AM
Greetings,
According to your query, the ExcelDemy team has created an Excel file with the solution. Please provide your email address here, we will send it to you in no time.

Otherwise, you can just follow the procedures as we have proceeded.

Step 1:
a. Our data set range is B4:F8.
b. The names of the fruits range is C4:F4.
c. Names of people range is B5:B8.

Step 2:
a. In cell C11, create a drop-down list with the range C4:F4.
b. Give a named range for the numbers of each fruit with its name (Ex: Named range = Mango for C5:C8).
c. Create another dropdown list dependant to the cell C11. Use the following formula in the Data Validation box from the Data tab to do this.
```
=INDIRECT($C$11)
```
Step 3:
a. In cell C15, insert the following formula for Mango.
```
=INDEX($B$5:$F$8,IF(IF($C$11=C4,MATCH(C4,$B$4:$F$4,0)," ")=(MATCH($C$11,$B$4:$F$4,0)),MATCH($D$11,C5:C8,0), " "),1)
```
b. AutoFill the formula with dragging right for three cells for more three fruits.

Step 4:
a. Now, you are done, select any name (Grapes) from the first drop-down list.
b. Then, select the number (6).
c. It will result in the person name (Kent).

Please, provide your further feedback if any queries needed.

Bhubon Costa Aug 3, 2022 at 1:50 PM

Greeting AIN,
Let’s say you want to add another column containing the CC email addresses. You can just copy the codes from here. Moreover, if you want to get the Excel file containing the codes, just give your email address.

Public Sub SendReminderMail()
'Declare the variables
    Dim XDueDate As Range
    Dim XRcptsEmail As Range
    Dim xMailContent As Range
    Dim xRngDn As Range
    Dim xCrtOut As Object
    Dim xValDateRng As String
    Dim xValSendRng As String
    Dim k As Long
    Dim xMailSections As Object
    Dim xFinalRw As Long
    Dim CrVbLf As String
    Dim xMsg As String
    Dim xSubEmail As String
'Declare variables for additional column
    Dim xCCEmail As String
    Dim xCCContent As Range
    
    On Error Resume Next
    
    'To select the date column insert a input box
    Set XDueDate = Application.InputBox("Select the column for Deadline/Due Date date column:", "ExcelDemy", , , , , , 8)
    If XDueDate Is Nothing Then Exit Sub
    
    'Insert a input box for selecting the recipients
    Set XRcptsEmail = Application.InputBox("Choose the column for the email addresses of the recipients:", "ExcelDemy", , , , , , 8)
    If XRcptsEmail Is Nothing Then Exit Sub
    
    'To enter the text mail, insert a input box
    Set xMailContent = Application.InputBox("In your email, choose the column with the reminded text:", "ExcelDemy", , , , , , 8)
    If xMailContent Is Nothing Then Exit Sub
    
    'To enter the CC mails in another column, insert a input box
    Set xCCContent = Application.InputBox("In your email, choose the column with the CC addresses:", "ExcelDemy", , , , , , 8)
    If xCCContent Is Nothing Then Exit Sub
    
    
    
    'Count rows for the due dates
    xFinalRw = XDueDate.Rows.Count
    Set XDueDate = XDueDate(1)
    Set XRcptsEmail = XRcptsEmail(1)
    Set xMailContent = xMailContent(1)
    Set xCCContent = xCCContent(1)
    
    'Set command to open MS Outlook Application
    Set xCrtOut = CreateObject("Outlook.Application")
    
    'Apply For loop to conduct the operation in each row one by one
    For k = 1 To xFinalRw
        xValDateRng = ""
        xValDateRng = XDueDate.Offset(k - 1).Value
        
        'Apply If condition for the Due Date values
        If xValDateRng <> "" Then
        
        'Condition set to send mail if the difference between due dates and current date is greater than 1 and less than 7 days
        'Means 1 < X< 7, X = Due Date - Current Date
        If CDate(xValDateRng) - Date <= 7 And CDate(xValDateRng) - Date > 0 Then
            xValSendRng = XRcptsEmail.Offset(k - 1).Value
            
            'Create the cc emails with the required variables
            xCCEmail = xCCContent.Offset(k - 1).Value
            
            'Create the subject, body and text contents with the required variables
            xSubEmail = xMailContent.Offset(k - 1).Value & " on " & xValDateRng
            
            CrVbLf = "<br><br>"
            xMsg = "<HTML><BODY>"
            xMsg = xMsg & "Dear " & xValSendRng & CrVbLf
            xMsg = xMsg & "Text : " & xMailContent.Offset(k - 1).Value & CrVbLf
            xMsg = xMsg & "</BODY></HTML>"
            
            'Create the email
            Set xMailSections = xCrtOut.CreateItem(0)
            
            'Define the position to place the Subject, Body and Recipients Address
            With xMailSections
                .Subject = xSubEmail
                .To = xValSendRng
                .CC = xCCEmail
                .HTMLBody = xMsg
                .Display
                
                '.Send
            
            End With
            Set xMailSections = Nothing
        End If
    End If
    Next
    Set xCrtOut = Nothing
End Sub

Hope this will work with your requirements.
Please, keep giving your feedbacks. The ExcelDemy team is always concerned about your queries.

Bhubon Costa Aug 3, 2022 at 11:51 AM

‘Thank you Dan for your comment. Let’s just your first row is 2 and last row is 7.

‘First condition: G = “BID”, H <= 10000.00, I = "Orders, Web" OR ="cXML Orders"

Sub Hide_Condition1()
Dim i As Integer
    For i = 2 To 5
' Cells (i,7) here 7 means G column and i will be 2, so it will be G2 cell and so on
        If Cells(i, 7).Value = "BID" And Cells(i, 8).Value <= 10000 And _
        (Cells(i, 9).Value = "Orders, Web" Or Cells(i, 9).Value = "cXML Orders") Then
            Rows(i).EntireRow.Hidden = True
        Else
            Rows(i).EntireRow.Hidden = False
        End If
    Next i
End Sub

‘Second condition: G =”MO”, H <10000.00

Sub Hide_Condition_2()
Dim i As Integer
    For i = 2 To 5
        If Cells(i, 7).Value = "MO" And Cells(i, 8).Value <= 10000 Then

            Rows(i).EntireRow.Hidden = True
        Else
            Rows(i).EntireRow.Hidden = False
        End If
    Next i
End Sub

‘Last Condition: G <10000.00, Apply Color

Sub Hide_Condition_3()
Dim i As Integer
    For i = 2 To 5
        If Cells(i, 7).Value < 10000 Then
        Cells(i, 7).Interior.Color = RGB(253, 233, 217)
        End If
    Next i
End Sub

‘Hope this answer your query.

Reply
Bhubon Costa Jul 12, 2022 at 4:05 PM

First of all, take cordial greetings from the ExcelDemy team. There could be some possible reasons due to the issue:
The inverted commas are not properly inserted in your code as they should be in VBA script.
The Sheet Names might not be inserted in VBA exactly as they are in the Excel file.
However, we have created an Excel file precisely in accordance with your requirements. We can send the Excel file if you provide your email address.
Otherwise, you can copy the codes below that we have used to make the Excel file with the right syntax. Please continue to provide us with feedback; ExcelDemy always welcomes your concerns.

Sub MoveRowBasedOnCellValue()
‘Declare Variables
Dim MAIN As Worksheet
Dim Project As Worksheet
Dim Claims As Worksheet
Dim TaM As Worksheet
Dim QUOTED As Worksheet
Dim PM As Worksheet
Dim FinalRow As Long
Dim Cell As Range

‘Set Variables
Set MAIN = Sheets(“Dataset2”)
Set Claims = Sheets(“CLAIMS”)
Set Project = Sheets(“Project”)
Set TaM = Sheets(“TaM”)
Set PM = Sheets(“PM”)
Set QUOTED = Sheets(“QUOTED”)

‘Type a Command to select the entire row
Selection.EntireRow.Select

‘Define destination sheets to move row
FinalRow1 = MAIN.Range(“A” & MAIN.Rows.Count).End(xlUp).Row
FinalRow2 = Claims.Range(“A” & Claims.Rows.Count).End(xlUp).Row
FinalRow3 = Project.Range(“A” & Project.Rows.Count).End(xlUp).Row
FinalRow4 = TaM.Range(“A” & TaM.Rows.Count).End(xlUp).Row
FinalRow5 = QUOTED.Range(“A” & QUOTED.Rows.Count).End(xlUp).Row
FinalRow6 = PM.Range(“A” & PM.Rows.Count).End(xlUp).Row
With MAIN

‘Apply loop for column F until last cell with value
For Each Cell In .Range(“F1:F” & .Cells(.Rows.Count, “F”).End(xlUp).Row)

‘Apply condition to match the “CLAIMS” value
If Cell.Value = “CLAIMS” Then

‘Command to Copy and move to a destination Sheet “CLAIMS”
.Rows(Cell.Row).Copy Destination:=Claims.Rows(FinalRow2 + 1)
FinalRow2 = FinalRow2 + 1

‘Apply condition to match the “PROJECT” value
ElseIf Cell.Value = “EQUIPMENT PROJECT” Then

‘Command to Copy and move to a destination Sheet “PROJECT”
.Rows(Cell.Row).Copy Destination:=Project.Rows(FinalRow3 + 1)
FinalRow3 = FinalRow3 + 1

‘Apply condition to match the “PROJECT” value
ElseIf Cell.Value = “CONTRACTING PROJECT” Then

‘Command to Copy and move to a destination Sheet “PROJECT”
.Rows(Cell.Row).Copy Destination:=Project.Rows(FinalRow3 + 1)
FinalRow3 = FinalRow3 + 1

‘Apply condition to match the “TaM” value
ElseIf Cell.Value = “T&M” Then

‘Command to Copy and move to a destination Sheet “TaM”
.Rows(Cell.Row).Copy Destination:=TaM.Rows(FinalRow4 + 1)
FinalRow4 = FinalRow4 + 1

‘Apply condition to match the “Quoted” value
ElseIf Cell.Value = “QUOTED” Then

‘Command to Copy and move to a destination Sheet “QUOTED”
.Rows(Cell.Row).Copy Destination:=QUOTED.Rows(FinalRow5 + 1)
FinalRow5 = FinalRow5 + 1

‘Apply condition to match the “SVC AGR” value
ElseIf Cell.Value = “SVC AGR” Then

‘Command to Copy and move to a destination Sheet “PM”
.Rows(Cell.Row).Copy Destination:=PM.Rows(FinalRow6 + 1)
FinalRow6 = FinalRow6 + 1
End If

Next Cell

End With

End Sub