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.

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.

Thanks a lot for your appreciation. You can also put your problems here, the ExcelDemy team is always in your assistance.

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.

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.

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.

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