Meraz Al Nahian

Hi VBANEWB, thanks for reaching out. Here, we declared Count variable first. So the VBA automatically accepts Count2 as a similar variable. So we didn’t need to declare it separately.

Hi, Mr. Marais, thanks for reaching out. The way I see it, your time starts when you start travelling to your workplace and the end time is when your work time is over. So, subtracting 8 and half hour from this period of time (end time – travel start time) will provide the overtime. Also, Sundays and Holidays will be double overtime. Using this idea, I developed the following formula:

=IF(OR(B3="Sunday",C3="Yes"),2*((E3-D3)-TIME(8,30,0)),(E3-D3)-TIME(8,30,0))

The image below is here for better clarification.

Hi, Tan Li Wen, thanks for reaching out. Here, I developed a solution to your problem. I made a second column for email body in column B as shown in the picture.

Here is the modified code. Use it following the steps described in the previous comment.

Private Sub Workbook_Open()

'variable declaration
Dim myOutlookApp As Object
Dim myOutlookMail As Object
Dim i As Long
Dim Receiver As String
Dim EmailBody As String
Dim WS As Worksheet

'create outlook application object
Set myOutlookApp = CreateObject("Outlook.Application")
'set worksheet
Set WS = ActiveSheet

'loop through rows
'starting from the second row assuming row 1 is the Header
For i = 2 To WS.Range("C" & Rows.Count).End(xlUp).Row
'check if the task is assigned today
If WS.Range("C" & i) = Date Then
'check status
If WS.Range("E" & i) = "In progress" Or WS.Range("E" & i) = "Outstanding" Then
'check if the email address is same as the previous one
If WS.Range("G" & i) = Receiver Then
'merge email bodies for same email addresses
EmailBody = EmailBody & vbCrLf & WS.Range("A" & i)
Else
'if the email address is different, send the previous email
'create a new email
If Receiver <> "" Then
'create a new mail item
Set myOutlookMail = myOutlookApp.CreateItem(0)

'compose the email
With myOutlookMail
'set the receiver email address
.To = Receiver
'set the subject of the email
.Subject = "Task Summary"
'set the body of the email
.body = EmailBody
'send email
.Send
End With

'release the mail object
Set myOutlookMail = Nothing
End If

'set the receiver and body for the new email
Receiver = WS.Range("G" & i)
If WS.Range("B" & i) <> "" Then
EmailBody = WS.Range("A" & i) & ". You need to finish the work " & WS.Range("B" & i)
Else
EmailBody = WS.Range("A" & i)
End If
End If
End If
End If
Next i

'send the last email (if exists)
If Receiver <> "" Then
Set myOutlookMail = myOutlookApp.CreateItem(0)

With myOutlookMail
.To = Receiver
.Subject = "Task Summary"
.body = EmailBody
.Send
End With

Set myOutlookMail = Nothing
End If

'release the Outlook application object
Set myOutlookApp = Nothing

'display a confirmation message
MsgBox "The emails have been sent successfully from your outlook"

End Sub

Here, I made changes in the marked portion of the following image. If there is a second message in the column B, it will be added to the main Email body as You need finish the work within ‘x’ days. If there’s no such thing in column B, only the part of the Email body in column A will be sent.

Hope this helps. If you have any more questions, please let us know in the comment sections.

Regards,
Md. Meraz Al Nahian
Team ExcelDemy

Hi Mr. Brown, thanks for reaching out. Regarding the solution of your problem, you just have to modify the code a bit. You can use the code of the 3rd method, but I don’t know why it takes 15 minutes to send the email to the receiver.
So, I used the code of the first method. I’m giving it here again with modification. See the image below to see where the modification is made.

Sub SendEmail01()
Dim Range_Select As Range
Dim Date_Range As Range
Dim Cell_Address As String
Dim Subject, Email_From, Email_To, Cc, Bcc, Email_Text As String
Dim Mail_Object, Single_Mail As Object
On Error Resume Next
Cell_Address = ActiveWindow.RangeSelection.Address
Set Range_Select = Application.InputBox("Select a range:", "Message Box", Cell_Address, , , , , 8)
If Range_Select Is Nothing Then Exit Sub
For Each Date_Range In Range_Select
If Date_Range.Value = Date - 4 Then
Subject = Application.InputBox("Subject: ", "Message", , , , , , 2)
Email_From = Application.InputBox("Send from: ", "Message Box", , , , , , 2)
Email_To = Application.InputBox("Send to: ", "Message Box", , , , , , 2)
If Email_To = "" Then Exit Sub
Cc = Application.InputBox("CC: ", "Message Box", , , , , , 2)
Bcc = Application.InputBox("BCC: ", "Message Box", , , , , , 2)
Email_Text = Application.InputBox("Message Body: ", "Message Box", , , , , , 2)
Set Mail_Object = CreateObject("Outlook.Application")
Set Single_Mail = Mail_Object.CreateItem(0)
With Single_Mail
.Subject = Subject
.To = Email_To
.Cc = Cc
.Bcc = Bcc
.Body = Email_Text
.Send
End With
End If
Next
End Sub

You wanted to send the Email if the deadline is past 4 days by the current date. So I subtracted 4 in the code.

Remember, your desired date has to be present in the date list. I’m going to give you an example here. In the date list, the date 10/22/2023 is present. It is past 4 days by today’s date which is 10/26/2023.

If the date is not present in the list, the code will work but it won’t send any email. If you have any confusion, follow method 1 to operate the macro. You can insert necessary Email addresses and the message easily. Hope this helps you. Thank you.

Hi, thanks for reaching out. Yes, there is a way to send emails with attachments using VBA. Here is the code for you.

Sub Send_Email_with_Attachment()
Dim MyOutlook As Object
Set MyOutlook = CreateObject("Outlook.Application")
Dim MyMail As Object
Set MyMail = MyOutlook.CreateItem(olMailItem)
MyMail.To = "[email protected]"
MyMail.CC = "[email protected]"
MyMail.BCC = "[email protected]"
MyMail.Subject = "Sending Email with VBA."
MyMail.Body = "This is a Sample Mail."
Attached_File = "F:\ExcelDemy\Attachment.xlsx"
MyMail.Attachments.Add Attached_File
MyMail.Send
End Sub

You just have to change the file location according to your perception and use some valid email addresses. Also, keep logged into your outlook account.

Hi Jason, thanks for reaching out. I’ve used your formula and it returns PO for some values. However, I think I have a better idea for your solution.

Here, I made a drop down list using the names of the dataset (C5:C13 range). My target is, whenever you select a name, Excel will highlight the cell where the name last appeared.

Here is the drop down list.

And here is the formula with XLOOKUP and ROW functions to determine the row of last appearance of the name selected from the drop down list.
=ROW(XLOOKUP(G4,$C$5:$C$13,$B$5:$B$13,,,-1))

To highlight the cell with the last occurrence, we need to set a formula for conditional formatting. So select the range of names (C5:C13) and then go to Home >> Conditional Formatting >> New Rule >> Use a formula to determine which cells to format and insert the formula below.
=ROW()=$G$5
[caption id="attachment_427897" align="aligncenter" width="750"] Click the image to get a detailed view.[/caption]

After that, choose a fill color to format the cell and click OK.

Finally, if you select any name from the drop down, you will see the cell highlighted where the name occurred for the last time in column C.

Here, we selected Martin from the drop down and you can see that the name Martin appears 3 times in column C. But only the C12 cell is highlighted as it is the last time the name Martin appeared.

Hello AC, thanks for reaching out. Here’s a solution to your problem.

Here, I have some addresses in two sheets. The addresses in the first sheet are in the left side of the following image. The addresses of the second sheet can be found in the drop down list.

And here is the formula to calculate the distance.
=ACOS(SIN(RADIANS(VLOOKUP(F3,Sheet2!A2:C33,2,FALSE))) *SIN(RADIANS(VLOOKUP(E3,A2:C29,2,FALSE))) +COS(RADIANS(VLOOKUP(F3,Sheet2!A2:C33,2,FALSE))) *COS(RADIANS(VLOOKUP(E3,A2:C29,2,FALSE))) *COS(RADIANS(VLOOKUP(F3,Sheet2!A2:C33,3,FALSE)-VLOOKUP(E3,A2:C29,3,FALSE)))) *6371

Thanks David for your feedback. The formatting of Mix PY, Mix CY and Mix Change being in percentage made it a bit confusing. We’ll update it soon.

Hello JC, thanks for reaching out. Here, the main formula for Mix Variance is:
Mix Variance = Revenue PY*(Price PY – Average Price PY)*(% of Mix CY – % of Mix PY). Although the Mix PY and CY were shown in percentage format in the article, we didn’t calculate these values as percentage. So in the formula, the difference of Mix CY and PY is divided by 100.

Please mention which method you are following, this will help us to understand the reason why this error is occurring. Thank you.

Hello Zeus, thanks for reaching out. If you want the sales data for Rowan and Ben in a single column, you simply insert the names Ben and Rowan in the new Name column (Cells B13 and B14). Putting the original formula in cell C13 will return the sales data about Rowan. Then you just drag the Fill icon downwards to copy the formula in C14 which will show the data of Ben. Putting names multiple times in the output part is not necessary. This will incident redundant result.

Thank you Julie for reaching out. The first method can be done to solve your problem. Just copy the data and paste this as linked picture into a new spreadsheet. Any change in your main spreadsheet will automatically be updated in the new sheet.

The message text in the second is actually the mail body. If you want to change the Subject of the email, you can simply change it in the code. Please follow the code in the picture below. This subject will be the same for all the mails.

Hello L.C., thank you for reaching out. The angles in the formula are used as input for the Sine and Cosine functions. So the conversion from degree to radian isn’t necessary. Whether I change the angle unit to radian or not, the value of the corresponding Sine or Cosine function will be the same as the angles remain the same.

Hello Melissa, thanks for reaching out. To answer your question, yes, you can export data from that PDF file to Excel. Just for a reminder, when you do that, please follow the steps.
1. Open the PDF file in Adobe Acrobat Pro.
2. Then select Tools >> Forms >> More Form Options >> Merge Data Files into Spreadsheets.
By doing this, you can export everything from your fillable PDF file to Excel Spreadsheet.

Hi Dav, thank you for reaching out. It’s unavoidable to have empty cells after the name of the players because we are splitting the adjacent cells to the Footballer Name column.

Hello sir, the process has been disabled since May 31. But to be sure, please check your Google account if you have the option ‘Less secure app access’ available. You can find this option from Manage Account >> Security >> Less secure app access. If you enable this option, then you can send Mails without Outlook.

Hey Praveen, thanks for reaching out. This XML files are actually in version 1 format.

Hello Shan, thanks for reaching out. It would be good if you share your code and the dataset file so that I could understand your problem clearly. What I understand from your comment, I think it is not the problem. You need to get your hidden rows back after you delete the filtered data. And to do that, just press CTRL+SHIFT+L. The command will remove the filter and return your hidden rows back.

Hi Dabrowski, thanks for reaching out. If you remove the ‘& LastRowFind(“convertxml”)’ part from the 7th line of the code. It will solve your problem. The function ‘LastRowFind’ causes to generate that extra file.

That’s great Griffin, I hope you get benefited from our articles.

Hi Bolton, thanks for reaching out. I think you need to use different code for your solution. The following code will create a user defined function to sum up the data based on the background color of cells.

Function SumBasedOnColor(mn_cell_color As Range, mn_range As Range)
Dim mn_sum As Long
Dim mn_colorIndex As Integer
mn_colorIndex = mn_cell_color.Interior.ColorIndex
For Each mn_CI In mn_range
If mn_CI.Interior.ColorIndex = mn_colorIndex Then
mn_sum = WorksheetFunction.Sum(mn_CI, mn_sum)
End If
Next mn_CI
SumBasedOnColor = mn_sum
End Function

After that, use the function like the following image.

There’s something important that you have to keep in mind. We referenced the cell F4 here and this cell background color was filled with yellow. The cell you reference should have the background color matched with the cells containing data.

Hi Steve, we are extremely sorry for your trouble. Named ranges are pretty useful when we need to use a range repeatedly. A lot of our readers are okay with it. Going through the dataset can help anyone to understand the defined range of a named range. But we are taking your feedback sincerely. We’ll apply your suggestion in the upcoming articles.

Hi Shilpa, thanks for the response. Here’s the solution to your question no 1

You can simply create a criteria similar to the methods of this article using dates. Suppose you want to see the sales information after May. Please watch the following image for the process. I created the criteria in G6 cell.

In order to solve the second question of your comment, please apply the method described in the Section 3.2 of this article.
Hope this helps to solve your queries.

Hello sir, thank you for the feedback. We are working hard on this matter, I hope we will provide you the solution in the upcoming tutorial pretty soon.

Hi! Thanks for asking. As the article was to provide some templates, I didn’t go in detail with the formula. Here, Invoice_Info is a named range. It refers to the range B12:J17 of the ‘template 1’ sheet. To create a named range, you need to select your desired range of cells, then go to Data Tab >> Define Name and after that, give a name of that range. The advantage is that, you can use that range by inserting it’s name anywhere in the worksheet or workbook. You don’t have to select the range every time you use it in a formula. Hope this solves your problem.

hi Chris, thanks for your response! A possible reason could be that you may not have the original or latest version of Adobe. If that’s the case, please install that. Hope this helps.

hello there, thanks for asking. I understand your problem. Although I haven’t found the exact solution to your problem yet, I can help you on sending email at a certain time in a day. The 3rd code sends email in the current date when you run it. You can add a scheduler in the code. The email will be sent at the time you set in the code. I added the code below. You can see that the time here is 16:18:00. So if you run the code before 16:18, and keep the corresponding Excel file open, the email will be sent at 16:18. You don’t need to rerun this code during this time. Hope this helps you a bit. I’ll be working on your problem too. If I get a solution, I’ll immediately add that in your reply.

Option Explicit
Private Sub Workbook_Open()
Application.OnTime TimeValue("16:18:00"), "SendEmail03"
End Sub
Sub SendEmail03()
Dim Date_Range As Range
Dim rng As Range
Set Date_Range = Range("B5:B10")
For Each rng In Date_Range
If rng.Value = Date Then
Dim Subject, Send_From, Send_To, _
Cc, Bcc, Body As String
Dim Email_Obj, Single_Mail As Variant
Subject = "Hello there!"
Send_From = "[email protected]"
Send_To = "[email protected]"
Cc = "[email protected]"
Bcc = ""
Body = "Hope you are enjoying the article"
On Error GoTo debugs
Set Email_Obj = CreateObject("Outlook.Application")
Set Single_Mail = Email_Obj.CreateItem(0)
With Single_Mail
.Subject = Subject
.to = Send_To
.Cc = Cc
.Bcc = Bcc
.Body = Body
.Send
End With
End If
Next
Exit Sub
debugs:
If Err.Description <> "" Then MsgBox Err.Description
End Sub