0
I’m trying to do a macro where I can identify if there’s a due date in the column L which expires within a month, and if any, launch a msgbox
and send an e-mail via Outlook
pasting the entire table (I1:O21) in the body of the email.
If there are no salaries with DateDiff
< 30, creates a msgBox
indicated that there are no payments in that month.
But for some reason there’s a mistake and I can’t identify.
The mistake:
Follows:
Sub Update_payments()
Dim dataMin As Date
Set myRange = Worksheets("Calendário Financeiro").range("L9:L19")
dataMin = Application.WorksheetFunction.Min(myRange)
If DateDiff("d", Now(), dataMinima) < 30 Then
msgbox "Atenção, há pagamentos de seguro dentro de 30 dias, verifique seu e-mail." + vbOKOnly
Dim rng As range
Sheets("Calendário Financeiro").Cells = "I1:O21"
Set rng = Selection.SpecialCells(xlCellTypeVisible)
rng.Copy Sheets("Calendário Financeiro").[A1]
Sheets("Calendário Financeiro").Activate: ActiveSheet.UsedRange.Select
ActiveWorkbook.EnvelopeVisible = True 'False
With ActiveSheet.MailEnvelope
.Introduction = "Olá, segue pagamentos de seguros dentro do mês:"
.Item.To = "[email protected]"
.Item.CC = ""
.Item.Subject = "TESTE Pagamento"
End With
Else
msgbox "Não há pagamentos de seguro para serem programados dentro do mês" + vbOKOnly
End If
End Sub
EDIT I ended up closing and opening the spreadsheet and had a different answer in the debugger:
What does that mean?
What error it displays?
– Denied
added the image with the error.
– Amadeu
You declare the variable after using it. Put
Dim dataMin As Date
beforedataMin = Application.WorksheetFunction.Min(myRange)
. The use ofOption Explicit
avoids this type of error as you would need to declare all variables.– danieltakeshi
I put it in the following order: `` Dim dataMin As Date Set myRange = Worksheets("Financial Calendar"). range("L9:L19") dataMin = Application.WorksheetFunction.Min(myRange)
– Amadeu
Error changed to "Runtime Error '6' - Overflow
– Amadeu
That line
Sheets("Calendário Financeiro").Cells = "I1:O21"
you are writing on the entire "I1:O21" spreadsheet, that is, over 65 billion cells. I believe what you want isSet rng = Sheets("Calendário Financeiro").Range("I1:O21").SpecialCells(xlCellTypeVisible)
. But your mistake is an overflow mistake.– danieltakeshi
remains the same D:
– Amadeu