1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Emailing from Excel on certain dates

Discussion in 'Business Applications' started by Beth.evans, Sep 9, 2016.

Thread Status:
Not open for further replies.
Advertisement
  1. Beth.evans

    Beth.evans Thread Starter

    Joined:
    Sep 9, 2016
    Messages:
    7
    Hi All,

    I have spent all day trying to figure out how to do this and when i think i have done it.. something is missing!!

    So, when the date in column A = Today, i need an email to be sent to the email address in column F stating the below:

    "Hi All,

    Engineer (Column B), is visiting Customer (Column C) today at (Column D) to complete (Column E)

    Many Thanks"

    I just cant get it to work!!

    Please can someone advise what i'm doing wrong?

    Sub eMail()
    Dim lRow As Integer
    Dim i As Integer
    Dim toDate As Date
    Dim toList As String
    Dim eSubject As String
    Dim eBody As String
    With OutLook.Application
    .ScreenUpdating = False
    .EnableEvents = False
    .DisplayAlerts = False
    If (Cells(i, 1) = TODAY < "") Then
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    toList = Cells(i, 7)
    eSubject = "Engineer " & Cells(i, 2) & "job list " & Cells(i, 1)
    eBody = "Hello All, " & vbCrLf & vbCrLf & "Engineer" & Cells(i, 2) & "Is at customer" & Cells(i, 3) & "in" & Cells(i, 4) & "and is" & Cells(i, 5) & vbCrLf & vbCrLf & "Many Thanks"

    On Error Resume Next
    With OutMail
    .To = toList
    .CC = ""
    .BCC = ""
    .Subject = eSubject
    .Body = eBody
    .bodyformat = 1
    .Send
    End With

    On Error GoTo 0
    Set OutMail = Nothing
    Set OutApp = Nothing
    Cells(i, 5) = "Mail Sent " & Date + Time
    End If

    ActiveWorkbook.Save
    With Application
    .ScreenUpdating = True
    .EnableEvents = True
    .DisplayAlerts = True
    End With
    End Sub
     

    Attached Files:

  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    I am glad to see that you have attempted to do this yourself.
    What you haven't said is what does or does not happen.
    Does Outlook open?
    Does it create the message?
    Does it populate To list?
    Does it attempt to send it?
    I also notice that your code has no actual error trapping, unless it is at "0" which is not shown.
     
  3. Beth.evans

    Beth.evans Thread Starter

    Joined:
    Sep 9, 2016
    Messages:
    7
    Hi OBP,

    Many thanks for your reply.

    It doesnt do any of the above, just keeps telling me the coding is wrong.

    Im not good with this technical stuff... Can you advise of the code i should be using, as im stuck :(

    Sub eMail()
    Dim lRow As Integer
    Dim i As Integer
    Dim toDate As Date
    Dim toList As String
    Dim eSubject As String
    Dim eBody As String
    With OutLook.Application
    .ScreenUpdating = False
    .EnableEvents = False
    .DisplayAlerts = False
    If (Cells(i, 1) = TODAY < "") Then
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    toList = Cells(i, 7)
    eSubject = "Engineer " & Cells(i, 2) & "job list " & Cells(i, 1)
    eBody = "Hello All, " & vbCrLf & vbCrLf & "Engineer" & Cells(i, 2) & "Is at customer" & Cells(i, 3) & "in" & Cells(i, 4) & "is" & Cells(i, 5) & vbCrLf & vbCrLf & "Many Thanks"

    On Error Resume Next
    With OutMail
    .To = toList
    .CC = ""
    .BCC = ""
    .Subject = eSubject
    .Body = eBody
    .bodyformat = 1
    .Send
    End With

    On Error GoTo 0
    Set OutMail = Nothing
    Set OutApp = Nothing
    Cells(i, 5) = "Mail Sent " & Date + Time
    End If

    ActiveWorkbook.Save
    With Application
    .ScreenUpdating = True
    .EnableEvents = True
    .DisplayAlerts = True
    End With
    End Sub
     
  4. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Well 2 things are immediately apparent, the variables
    OutApp
    and
    OutMail
    have not been dimensioned with a Dim statement.
    What I suggest that you do is add in some proper error trapping, so between the
    End With
    End Sub
    add in
    Exit sub
    Err_email:
    MsgBox Err.Description

    and change this line
    On Error GoTo 0
    to
    On Error GoTo Err_email

    This code should tell you which line of code or operation is causing the problem.
    Can I ask where you got the code from?
    As there are a lot of examples on this Forum of how to do what want and there is a Search facility to find them.
     
  5. Beth.evans

    Beth.evans Thread Starter

    Joined:
    Sep 9, 2016
    Messages:
    7
    Thank you.

    I have changed as required but when i try to run it says 'Object Required'

    I got the code from this website which is why im confused as to where im going wrong.

    Are you able to assist by setting up the coding on the document attached for me so i can copy it onto my sheet?

    Many Thanks
     

    Attached Files:

  6. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    I will take a look and get back to you.
     
  7. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    The current code will only check one date cell for sending an email, did you want it to check them all?
    If so will they be in date order?
     
  8. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    This version works for just the first row of data.
    Try it out for yourself, it should send me an email.
    Note that I have changed the first 4 cells in column 6 to text so that it accepts emails as text for the "ToList".

    For those interested here is the new code

    Dim lRow As Integer
    Dim i As Integer
    Dim toDate As Date
    Dim toList As String
    Dim eSubject As String
    Dim eBody As String
    With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .DisplayAlerts = False
    End With
    i = 2
    If (Cells(i, 1) = Date) Then
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    toList = Cells(i, 6)
    eSubject = "Engineer " & Cells(i, 2) & " job list " & Cells(i, 1)
    eBody = "Hello All, " & Chr$(13) & Chr$(13) & "Engineer " & Cells(i, 2) & " Is at customer " & Cells(i, 3) & " in " & Cells(i, 4) & " is " & Cells(i, 5) & Chr$(13) & Chr$(13) & "Many Thanks"
    On Error GoTo Err_email

    With OutMail
    .To = toList
    .CC = ""
    .BCC = ""
    .Subject = eSubject
    .Body = eBody
    .bodyformat = 1
    .Send
    End With


    On Error GoTo Err_email
    Set OutMail = Nothing
    Set OutApp = Nothing
    Cells(i, 5) = "Mail Sent " & Date + Time
    End If

    ActiveWorkbook.Save

    With Application
    .ScreenUpdating = True
    .EnableEvents = True
    .DisplayAlerts = True
    End With

    Exit Sub
    Err_email:
    MsgBox Err.Description
     
    Last edited: Sep 12, 2016
  9. Beth.evans

    Beth.evans Thread Starter

    Joined:
    Sep 9, 2016
    Messages:
    7
    Yay it works!!

    Thank you for your help OBP!
     
  10. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Now do you need it to go down the column and check the dates?
    Are the dates going to be in date order?
     
  11. Beth.evans

    Beth.evans Thread Starter

    Joined:
    Sep 9, 2016
    Messages:
    7
    Hi OBP,

    Yes i need it to recognise that on each day it will send an email alert advising who is doing what jobs on that day.

    Many Thanks

    Beth
     
  12. Beth.evans

    Beth.evans Thread Starter

    Joined:
    Sep 9, 2016
    Messages:
    7
    And yes, it will be in date order :)
     
  13. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Ok, I will take another look.
     
  14. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Beth what are you intending doing about days that may be missed, ie due to illness or similar?
     
  15. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Beth, this does what you want. Note I have added an email sent date, which the code fills in when the email is sent.

    You must be careful with VBA code, it uses absolute referencing, so unlike the rest of the functions in Excel VBA IS NOT updated if you make any structural changes to the worksheet.
    ie if you move, delete or add any columns within those used by the code, the code will fail.
     

    Attached Files:

  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1177806

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice