+ Reply to Thread
Results 1 to 10 of 10

Embedding date function in Excel VBA

  1. #1
    Registered User
    Join Date
    01-26-2023
    Location
    Southfield, Michigan
    MS-Off Ver
    Excel 365
    Posts
    7

    Embedding date function in Excel VBA

    Hello,
    Based on my very limited knowledge about VBA I must once again ask for help, but I'm a fast learner.
    I have a macro that sends emails. The outlook email body needs to contain the first day of the next month.
    I have this line in my macro:

    strbody = strbody & "the cases listed below are scheduled to be closed out in our system on " & "<b>Format(DateSerial(Year(Now()), Month(Now()) + 1, 1), (dd-mm-yyyy))</b>" & "." & "<br>"

    The current output to outlook looks like this:

    the cases listed below are scheduled to be closed out in our system on Format(DateSerial(Year(Now()), Month(Now()) + 1, 1), (dd-mm-yyyy)).

    Cant find how to get the date function to work.
    I'm using Office 365.
    Any help is greatly appreciated!
    Thank you in advance!

  2. #2
    Valued Forum Contributor ranman256's Avatar
    Join Date
    07-29-2012
    Location
    Kentucky
    MS-Off Ver
    Excel 2003
    Posts
    1,176

    Re: Embedding date function in Excel VBA

    Use :
    vMyDate = date() 'or some other date

    'add 1 month
    vMyDate = dateadd("m",1,vMyDate)

    'format
    format(vMyDate,"dd-mm-yyyy")

  3. #3
    Registered User
    Join Date
    01-26-2023
    Location
    Southfield, Michigan
    MS-Off Ver
    Excel 365
    Posts
    7

    Re: Embedding date function in Excel VBA

    Thank you! I'm sorry, still don't know how to insert the function in the strbody. Tried various ways.

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,420

    Re: Embedding date function in Excel VBA

    Try:
    Please Login or Register  to view this content.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  5. #5
    Registered User
    Join Date
    01-26-2023
    Location
    Southfield, Michigan
    MS-Off Ver
    Excel 365
    Posts
    7

    Re: Embedding date function in Excel VBA

    Thank you! Getting closer, it now returns "44958", could that be the boolean date?

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,420

    Re: Embedding date function in Excel VBA

    Ok, maybe some more quotes:
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    01-26-2023
    Location
    Southfield, Michigan
    MS-Off Ver
    Excel 365
    Posts
    7

    Re: Embedding date function in Excel VBA

    That almost worked, lol. But you pointed me in the right direction.
    Played around with it and removed some quotes and parenthesis. It's now working!
    Here is the final function in case you need it in the future: Format(DateSerial(Year(Now()), Month(Now()) + 1, 1), "mm-dd-yyyy")
    Thank you so much for your help, I really appreciate it. Enjoy the rest of your day!
    Larz

  8. #8
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,420

    Re: Embedding date function in Excel VBA

    You're welcome.


    I wasn’t sure what format you were aiming for. And, without putting it into the VBA Editor, it's not always obvious (to me) whether you need to double up the quotes.


    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

  9. #9
    Registered User
    Join Date
    01-26-2023
    Location
    Southfield, Michigan
    MS-Off Ver
    Excel 365
    Posts
    7

    Re: Embedding date function in Excel VBA

    Understood, sometimes it can be trial and error. Still new to VBA but persistency pays off. Thanks again for your help, added to your reputation and marked the thread solved.
    Cheers!

  10. #10
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,420

    Re: Embedding date function in Excel VBA

    You're welcome. Thanks for the rep.

+ 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. Embedding a roundup formula in an if function
    By templeowls in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-21-2019, 08:48 PM
  2. Embedding Calendar into cell to select date
    By peakoverload in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-25-2017, 11:27 AM
  3. embedding address from cell in function
    By koamapping in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-26-2013, 08:53 AM
  4. [SOLVED] Excel function to very basic HTML converter for embedding in XBRL (XML) files
    By uselessnut in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 12-25-2012, 12:23 PM
  5. Embedding index function in sumproduct
    By everton9 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-05-2007, 04:23 PM
  6. [SOLVED] embedding text in a function
    By [email protected] in forum Excel General
    Replies: 3
    Last Post: 08-07-2006, 02:55 AM
  7. Custom Function Key and embedding
    By Jo in forum Excel General
    Replies: 0
    Last Post: 01-02-2005, 07:06 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