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.

Excel coding to send an e-mail on expiry dates.

Discussion in 'Business Applications' started by ckfishe, Mar 11, 2013.

Thread Status:
Not open for further replies.
Advertisement
  1. ckfishe

    ckfishe Thread Starter

    Joined:
    Mar 11, 2013
    Messages:
    2
    Good Afternoon all,

    I have been trying to make my register e-mail when an expiry date is approaching. Now I've made a code to try and get this to work however I get a error 400 message.
    The e-mail it needs to go to is in (A1)
    The expiry dates are in row (P3:R164)
    I would like it to also send the persons name in (J3:L164) within the e-mail as I havent worked this out yet.

    Attached is the example workbook.

    The code I am using is:
    HTML:
     
    Sub Email()
    Dim objExcel
    Dim objOutlook
    Dim objMail
    Dim objWB
    Dim objWS
    Dim vCell
    Set objExcel = CreateObject("Excel.Application")
    Set objOutlook = CreateObject("Outlook.Application")
    objExcel.DisplayAlerts = False
    objExcel.Workbooks.Open ("C:\Test.xls")
    Set objWB = objExcel.ActiveWorkbook
    Set objWS = objWB.ActiveSheet
    For Each vCell In objWS.Range("P3:R164" & objWS.Cells(objWS.Rows.Count, "S").End(-4162).Row).Cells
    If FormatDateTime(vCell) <= FormatDateTime(Date) Then
    If vCell.Offset(0, 1).Value <> "YES" Then
    Set objMail = objOutlook.CreateItem(olMailItem)
    objMail.To = "[EMAIL="[email protected]"][email protected][/EMAIL]"
    objMail.Subject = vCell.Offset(0, -8).Value & "ChemAlert Password About to Expire"
    objMail.Body = "COURSE TITLE - " & vCell.Offset(0, -8).Value & vbCrLf & _
    "APPROVING STATE - " & vCell.Offset(0, -7).Value & vbCrLf & _
    "APPROVING BODY - " & vCell.Offset(0, -6).Value & vbCrLf & _
    "PROFESSION - " & vCell.Offset(0, -5).Value & vbCrLf & _
    "CEUs - " & vCell.Offset(0, -4).Value & vbCrLf & _
    "APPROVAL NUMBER - " & vCell.Offset(0, -3).Value & vbCrLf & _
    "DATE RECEIVED - " & vCell.Offset(0, -2).Value & vbCrLf & _
    "EXPIRATION DATE - " & vCell.Offset(0, -1).Value
    objMail.Send
    vCell.Offset(0, 1).Value = "YES"
    End If
    End If
    Next
     
    objWB.Save
    objWB.Close
    objExcel.Quit
    objOutlook.Quit
    Set objExcel = Nothing
    Set objWB = Nothing
    Set objWS = Nothing
    Set objMail = Nothing
    Set objOutlook = Nothing
    End Sub
     

    Attached Files:

  2. Keebellah

    Keebellah Hans Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,342
    Hi, welcome to the forum.
    Have you run a search on this forum, there are many posts with similar questions and answered too.
    All you will need to do is probably edit it for your needs.
    See if you can find these, no need to reinvent the wheel :)
     
  3. ckfishe

    ckfishe Thread Starter

    Joined:
    Mar 11, 2013
    Messages:
    2
    Thanks for your response however I have used the coding already from the website however am unsure on what I currently have wrong that's not allowing this coding as it apparently worked for another person however not myself.
     
  4. XCubed

    XCubed

    Joined:
    Feb 21, 2013
    Messages:
    520
    Hi

    Its difficult to diagnose the problem because the VBA code is password protected. Nonetheless, here are some comments that may help in figuring this out;

    in this line

    objExcel.Workbooks.Open ("C:\Test.xls")objExcel.Workbooks.Open ("C:\Test.xls")

    ("C:\Test.xls") needs to be changed to the correct file name and path

    The "Body" text does not correlate with what you have in the spread sheet e.g.

    APPPROVING STATE, CEU, PROFESSION don't appear in the data

    I would get rid of the merged cells - they are unnecessary and will make the code more complex than it needs to be.

    If you can;
    reformat the sheets to get rid of the merged cells
    create a mock-up of the resulting email noting where each element is sourced in the spread sheet
    describe how and when the emails should be sent

    then I can try to get the code to work.
     
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/1092627