+ Reply to Thread
Results 1 to 11 of 11

Change Email subject based on date and time

  1. #1
    Registered User
    Join Date
    01-21-2014
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    6

    Change Email subject based on date and time

    Hello

    I have a Macro that currently saves the active sheet as a PDF and attaches it to an outlook email. I would like to modify the macro to add the shift name during which it is emailed out on. For example, if the Email is sent out on a Monday morning between 4am and 8am then the subject should read "12B GOC Turnover" however if it was sent out Monday night between 4pm and 8pm then the subject should read "12A GOC Turnover".

    We have different shift names during the week that I can add myself once I understand how to code this. I am not much of a programmer and any help is appreciated. Here is the section of the code where it sends the email out.

    Please Login or Register  to view this content.
    Last edited by ceminess; 02-04-2018 at 12:38 AM.

  2. #2
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: Change Email subject based on date and time

    Deleted because OP complied to forum rules.
    Last edited by bakerman2; 01-30-2018 at 02:00 AM.
    Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
    You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.

  3. #3
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: Change Email subject based on date and time

    And where can we get "12B GOC Turnover" and "12A GOC Turnover" ?
    How can we know what code to use as subject ?

  4. #4
    Registered User
    Join Date
    01-21-2014
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    6

    Re: Change Email subject based on date and time

    Hello, Sorry this is my first time posting here. It is currently not in the code. Currently it just takes the name of the active sheet in excel and makes that the subject. I'd like to instead have the subject include the shift name, such as 12A or 12B, based on the time of day and day of week it is when the macro is ran.

  5. #5
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: Change Email subject based on date and time

    That's what I'm asking. Is there a list somewhere that has the shiftnames ?

  6. #6
    Registered User
    Join Date
    01-21-2014
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    6

    Re: Change Email subject based on date and time

    We use Sheet 2 for populating drop down boxes in sheet 1. A105 - 109 has them; 12A, 12B, 10A, 10B, 10C. It doesn't have the shift day and times, but I can add them if it helps.

  7. #7
    Registered User
    Join Date
    01-21-2014
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    6

    Re: Change Email subject based on date and time

    Also, here are the day and times I am thinking of. I hope this makes sense.

    12A Sunday, Monday, Tuesday; 4am to 8am
    12B Saturday, Sunday, Monday; 4pm to 8pm
    10A Tuesday - Friday; 1pm to 7pm
    10B Tuesday - Friday; 8pm to Midnight
    10C Wednesday - Saturday; 4am to 10am

    If the report is sent outside of these times, then I just want it to use the name of sheet 1.

    Thank you!

  8. #8
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: Change Email subject based on date and time

    Here's a start.
    Please Login or Register  to view this content.
    Last edited by bakerman2; 02-04-2018 at 03:16 AM. Reason: translated weekdays from dutch to english

  9. #9
    Registered User
    Join Date
    01-21-2014
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    6

    Re: Change Email subject based on date and time

    Thank you! It looks like this will work great! Just in case I need to change the times around, can you explain how I can read cTime?

  10. #10
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: Change Email subject based on date and time

    Times and dates are considered numbers in XL so this line
    Please Login or Register  to view this content.
    converts the actual time into a decimal number.
    When you put the time in your worksheet and convert it to a decimal number you'll see what's behind the comma as the time in decimal.

    PS Thanks for rep+.

  11. #11
    Registered User
    Join Date
    01-21-2014
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    6

    Re: Change Email subject based on date and time

    Oh Nice! I understand now. You're welcome! Thank you again!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. How to change email file from Subject to Sender's email address?
    By london7871 in forum Outlook Formatting & Functions
    Replies: 0
    Last Post: 09-25-2017, 11:13 PM
  2. [SOLVED] Help comparing two files by subject+Date, subject+DatePlus1, Subject+DateMinus1
    By welchs101 in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 07-11-2017, 07:37 AM
  3. Extracting Email Received Date and Time and Subject line from Inbox (WebMail)
    By Naveed Raza in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-28-2015, 01:19 AM
  4. [SOLVED] Enter current date and time in subject of macro email
    By ExcelFailure in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-13-2013, 11:15 AM
  5. changing date in email subject line with vba
    By ConfusedaboutVBA in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-09-2012, 07:11 AM
  6. Want to populate email fields FROM, TO, CC, Subject based on a named range
    By mbhc77 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-29-2012, 12:24 PM
  7. Want to change attached file name & subject line in the coding : Email Range
    By Kapil007 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-30-2010, 07:45 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1