Results 1 to 14 of 14
  1. #1
    New Lounger
    Join Date
    Nov 2001
    Location
    Long Island, New York
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    MSOffice Gurus - Help (2000 & XP)

    MS Office Gurus,

    We would like to have a macro defined, to be used in Outlook, that when a hot-key is pressed ( like Ctrl+@ ) would add a date & time text into the note field of an appointment and then allow us to keep typing after the insertion of this information.

    We would like the text that is inserted to look like this:
    Date@Time --> for example --> 06/03/02@10:22
    the date to be in format: mm/dd/yy and the time to be in format: 24hour clock
    with the date separated from the time by an @ sign.

    It is our understanding that this could be accomplished with the creation of a macro in Visual Basic??? Problem is, that no one here can even pronounce "Visual Basic" much less understand how to use it.

    Could we persuade someone to write this for us and then explain how to call the macro with a pre-defined hot-key when in an Outlook appointment note field?
    PS -- A journal entry will not serve our purposes

    There is a virtual cup off coffee in it for you [img]/forums/images/smilies/smile.gif[/img]

    Thanks for taking the time to help
    Larry

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: MSOffice Gurus - Help (2000 & XP)

    <P ID="edit" class=small>(Edited by JohnBF on 16-Jun-03 16:09. Improved code and instructions for placing in menu.)</P>By "a date & time" do you mean the date and time that the keystroke is entered? I have the code for that here, but ...

    Option Explicit
    Sub StampDateTimeInMeetingBody()
    Dim objAppt As AppointmentItem
    Dim strActiveWindowType As String
    On Error Resume Next
    strActiveWindowType = TypeName(Application.ActiveWindow)
    Select Case strActiveWindowType
    Case "Inspector"
    Set objAppt = ActiveInspector.CurrentItem
    Case "Explorer"
    Set objAppt = ActiveExplorer.Selection.Item(1)
    End Select
    If objAppt Is Nothing Then
    MsgBox "No meeting or appointment is selected"
    Else
    objAppt.Body = Format(Now, "mm/dd/yyyy @ hh:mm") & vbLf & vbLf & objAppt.Body
    If strActiveWindowType = "Explorer" Then objAppt.Save
    End If
    Set objAppt = Nothing
    End Sub

    Outlook does not support hotkeys the way that Word or Excel does. Your choices are to add this to a button, and click the biutton, or to add it to a menu and get to the menu by using Alt-x, x, keystrokes (for example, add it to the Actions Menu, as "St@mp Date & Time", would require Alt-A, Shift-2).

    To set it up, open Outlook, select Tools, Macros Security, Low. Close and reopen Outlook. In Outlook press Alt-F11, and see if there is a Module in the top left Window (looks like File Explorer). If not, use File Insert to insert a Module. Double click the Module folder icon. In the large right side window paste the code as above. Click the File Save button in the menus of that Window. Close the window, select Tools, Macros Security, Medium. Close and restart Outlook. You can now run the code from the Macro window that pops up when you press Alt-F8, by double-clicking "AddDateTimeToAppointBody".

    To add it to a Menu or to a Button manually, go into View, Toolbars, Customize, Macros, drag the macro to the toolbar or the menu (for your purposes, do this for both the menu that occurs in general Calendar view, and the menu when within an open Calendar item). Then edit the text and the icon as you wish. To use the Alt-x, x keys, you must create a Menu item and insert an Ampersand in front of the hot-letter in Menu item, like this: St&@mpDate&&TimeIn Appointment, which you run to by pressing Alt-A, Shift-2, or clicking through the menu.
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    New Lounger
    Join Date
    Nov 2001
    Location
    Long Island, New York
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MSOffice Gurus - Help (2000 & XP)

    John,
    I had the time over the weekend to try your nifty answer to my Guru's Question. It worked great but had a few problems.

    It does not add a new timedate stamp in the text section of an appointment below a time date stamp that it had placed previously. The new stamp is added to the top of the note section (before the one that was placed at an earlier time) and removes any formatting that had been done in the note section (ie: colors, italics, bolding, etc). Can a new timedate stamp be added below one previously added and not erase any formatting done in the text body?

    I have taken a few screen shots to help explain the problem if you would like to see them. Unfortunately the file size is 918K (had put them in a Wod Doc and made notes). The jpg's themselves are 380 each.

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: MSOffice Gurus - Help (2000 & XP)

    Would it be acceptable if the time stamp was at the end of the text instead of the beginning? If so, they can easily be in the "most recent last" order.

    If not, some explanation is in order. In Outlook the freeform text portion of a message or task or appointment is a single "object" called "body". Body doesn't have any sub-objects such as paragraph, so to do what you want would require more complicated manipulation of the text content of the body object, which I haven't played with. (I thought the behavior I had makes sense in terms of putting the most recent time on top, it's the way I think.) To manage the code that your request will require it would help if we added expository text to the timestamp, such as "Timestamp: mm/dd/yyyy @ hh:mm", because searching for "Timestamp: " would help in coding. This way code can loop through the entire text and find the last occurence of "Timestamp: " and then manipulate the text to add the new timestamp.

    The issue of maintaining text format is more difficult. I have to check, but I think the format of an appointment Body is RTF, and when RTF is manipulated by code, the entire body defaults to the default font etc., though it remains formatted as RTF. To handle the formatting would require more complex handling by calling an instance of Word and handling everthing within Word. I think it can be done but it becomes yet another layer of complexity and much more code.
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    New Lounger
    Join Date
    Nov 2001
    Location
    Long Island, New York
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MSOffice Gurus - Help (2000 & XP)

    John,
    Was at an Autocad seminar these past few days which is why I didn't get back to you earlier.
    This appears to be getting a little too complicated.
    We'll just change the way we do things and put the most recent post on the top of the page of the page. The color & formatting is another story and I'll have to look into it to see if there is another way around that issue.
    Thank You for all the help.
    Larry

  6. #6
    5 Star Lounger
    Join Date
    May 2001
    Location
    Washington, USA
    Posts
    750
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MSOffice Gurus - Help (2000 & XP)

    I don't know any other way to handle the formatting either. But I was wondering on the time stamp sequence if a change from

    objAppt.Body = Format(Now, "mm/dd/yyyy @ hh:mm") & vbLf & vbLf & objAppt.Body

    to

    objAppt.Body = objAppt.Body & vbcr & vbcr & Format(Now, "mm/dd/yyyy @ hh:mm") & vbLf & vbLf & objAppt.Body & vbcr

    would work?

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: MSOffice Gurus - Help (2000 & XP)

    Won't that duplicate all the prior text in objAppt.Body?
    -John ... I float in liquid gardens
    UTC -7ąDS

  8. #8
    5 Star Lounger
    Join Date
    May 2001
    Location
    Washington, USA
    Posts
    750
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MSOffice Gurus - Help (2000 & XP)

    Hi John. I believe it will replace the body and append the stamp to the beginning.

  9. #9
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: MSOffice Gurus - Help (2000 & XP)

    If there is existing text content in the body, I end up with something like this:

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~
    06/25/2003 @ 15:48

    This is the test text in the Body area of the Appointment. This would say blah, blah, blah.

    06/25/2003 @ 15:51

    06/25/2003 @ 15:48

    This is the test text in the Body area of the Appointment. This would say blah, blah, blah.


    06/25/2003 @ 15:51

    06/25/2003 @ 15:48

    This is the test text in the Body area of the Appointment. This would say blah, blah, blah.

    06/25/2003 @ 15:51

    06/25/2003 @ 15:48

    This is the test text in the Body area of the Appointment. This would say blah, blah, blah.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~

    etcetera.
    -John ... I float in liquid gardens
    UTC -7ąDS

  10. #10
    New Lounger
    Join Date
    Nov 2001
    Location
    Long Island, New York
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MSOffice Gurus - Help (2000 & XP)

    Gurus,
    We have been tossing around alternatives for this formatting issue in the subject body of an appointment. The general concensus here is that, we would like to investigate further any way to have the new DateTimeStamp and still retain formatting that was applied to the notes in the subject body of the appointment.

    What we do here is have an appointment for all the calls made in a day. The calls are listed by time. The formatting designates whether the call is incoming or out going or a repeat call. Notes are usually in italics with the called datetime emboldened. This allows us to search for calls by topic and easily insert them into a document that we ship to our clients when there is a difference of opinion on whether a call was made or not and when and on what topic we spoke.

    I can be reached back-channel on this if you would so desire.

    Thanks
    Larry

  11. #11
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: MSOffice Gurus - Help (2000 & XP)

    Larry, I can't commit to assisting you with this issue, perhaps other more capable Loungers may be interested. I will comment that your use of Outlook for call tracking is unusual; and it sounds like TimeSlips or a less expensive time tracker would be a better way to go than the way you are trying to use Appointments in Outlook.

    Also, Larry, do not be offended, you are not in violation of it, but note <!rule=17>Rule 17<!/rule>.
    -John ... I float in liquid gardens
    UTC -7ąDS

  12. #12
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: MSOffice Gurus - Help (2000 & XP)

    Regarding your original inquiry, we had some macros and a toolbar some time back to "paste" the date and time. Some brief digging turned up these:

    <pre>Sub TypeDate()
    Dim doClip As DataObject
    Set doClip = New DataObject 'instantiate DataObject
    doClip.SetText Format(Now, "mm/dd/yy"), 1 'set dataobject to short date
    doClip.PutInClipboard 'transfer contents to clipboard
    SendKeys "^v" 'paste date wherever the insertion point is
    Set doClip = Nothing 'clear object
    End Sub

    Sub TypeTime()
    Dim doClip As DataObject
    Set doClip = New DataObject 'instantiate DataObject
    doClip.SetText Format(Now, "hh:nn:ss"), 1 'set dataobject to current time
    doClip.PutInClipboard 'transfer contents to clipboard
    SendKeys "^v" 'paste date wherever the insertion point is
    Set doClip = Nothing 'clear object
    End Sub</pre>

    Note that your project needs to have a reference to the MSForms object library in order to use early binding for DataObject. Does this help bypass the changed formatting problem?

  13. #13
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: MSOffice Gurus - Help (2000 & XP)

    It works for me in Outlook 2000. Oddly, MS Forms did not appear as a Library, I had to browse to it at winntsystem32FM20.DLL. Thanks, Jefferson.
    -John ... I float in liquid gardens
    UTC -7ąDS

  14. #14
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: MSOffice Gurus - Help (2000 & XP)

    It will get added automatically when you Insert a UserForm, but otherwise it hides. I guess MS doesn't think you'll need it otherwise??

Posting Permissions

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