Premium Excel Course Now Available!

Build Professional - Unbreakable - Forms in Excel

45 Tutorials - 5+ Hours - Downloadable Excel Files

Instant Access! - Lifetime Access!
View Course

Sending automated email

Macros, VBA, Excel Automation, etc.
Forum rules
This forum is closed.

All new posts should be made at our new Excel Forum at TeachExcel.com.

Sending automated email

Postby Iffy » Fri Nov 11, 2016 11:25 am

sheet.png
Hi Everyone
I am new to VBA and I have been trying fir the past weeks to get a code to work and I need help.
Anyways I have a spread sheet that has some dates in it and I need excel to send email notifications to everyone that has access to the file on a specific date reminding them to update it.
My problem is that the code does not seem to effect the processes on the due date but when I run/test is independently it runs smoothly but nothing happens on the due date.

The final codes I used which I thought will work but didn't is

Code: Select all
Sub datesexcelvba()
Workbooks("NAME.xlsm").Worksheets("Sheet2").Activate
Dim mydate1 As Date
 Dim mydate2 As Long
 Dim x As Long
 
 Dim tod As Date
 tod = Now()
 
 For x = 2 To 10
 
 mydate1 = Cells(x, 2).Value
 mydate1 = mydate2

'If cell.Value = mydate2 And cell.Value <> “” Then
If mydate1 >= tod Then
 Workbooks("NAME.xlsm").Worksheets("Sheet2").Range("B" & x).Interior.ColorIndex = 3
 Workbooks("NAME.xlsm").Worksheets("Sheet2").Range("B" & x).Font.ColorIndex = 2
 Workbooks("NAME.xlsm").Worksheets("Sheet2").Range("B" & x).Font.Bold = True
 
End If
 Next x
 SendReminderMail
 End Sub

ub SendReminderMail()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strto As String, strcc As String, strbcc As String
    Dim strsub As String, strbody As String

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    strto = "email"
    strcc = ""
    strbcc = ""
    strsub = "Tracking sheet Notification"
    strbody = "Hi, The  Spreadsheet has to be updated for this week. Kindly ignore if already updated."

    With OutMail
        .To = strto
        .CC = strcc
        .BCC = strbcc
        .Subject = strsub
        .Body = strbody
        .Display
        End With
    SendKeys "%{S}"
    Set OutMail = Nothing
    Set OutApp = Nothing
debugs:
    If Err.Description <> "" Then MsgBox Err.Description

End Sub

I used the sendkey because the server I am working on has security restrictions which would not allow automated emails to be sent.

Guys please help
  • 0

You do not have the required permissions to view the files attached to this post.
Iffy
Rookie
 
Posts: 1
Joined: Nov 11, 2016
Reputation: 0
Excel Version: 2010

Re: Sending automated email

Postby NoSparks » Sat Nov 12, 2016 11:33 am

Try your datesexcelvba macro like this with the added message box and you will see what Excel is working with.

Code: Select all
Sub datesexcelvba()
    Workbooks("NAME.xlsm").Worksheets("Sheet2").Activate
    Dim mydate1 As Date
    Dim mydate2 As Long
    Dim x As Long
   
    Dim tod As Date
    tod = Now()
   
    For x = 2 To 10
       
 MsgBox "The numeric value Excel is using for tod is    " & CDbl(tod) & vbLf & _
 "The Excel numeric value of the date being considered is  " & Cells(x, 2).Value2 & vbLf & _
 "If tod equaled Date() instead of Now(), Excel would see it as  " & CDbl(Date)
           
        mydate1 = Cells(x, 2).Value
        mydate1 = mydate2
       
            If mydate1 >= tod Then
                Workbooks("NAME.xlsm").Worksheets("Sheet2").Range("B" & x).Interior.ColorIndex = 3
                Workbooks("NAME.xlsm").Worksheets("Sheet2").Range("B" & x).Font.ColorIndex = 2
                Workbooks("NAME.xlsm").Worksheets("Sheet2").Range("B" & x).Font.Bold = True
            End If
    Next x
    SendReminderMail
End Sub
  • 0

NoSparks
Excel Hobbyist
 
Posts: 637
Joined: May 28, 2014
Reputation: 103
Excel Version: 2010


Return to Macros and VBA Questions

Who is online

Users browsing this forum: No registered users and 2 guests