About

My name is David Lee. I am an information technology professional with more than 30 years of experience in the field. My experience covers almost every aspect of IT from programming (I had to punch my own cards when I started) to management. It’s more than a profession for me though. It’s a calling. I am passionate about technology and its use. Since 2007 Microsoft has rewarded that passion by selecting me as an Outlook MVP (Most Valuable Professional). My other passions include coffee, I’d hang an IV drip if I could, and college basketball. Go Blue Devils!

148 comments on “About

  1. I found your VB program KillOverdueReminders. It works just as you said it would. Fantastic!!! Your directions were perfect. I would like to kill reminders based on the APPOINTMENT date and time rather than the REMINDER. It appears to me that I need to replace “olkReminder.NextReminderDate” with a different variable. Can you tell me what it is and how to do that?
    Happy Holidays!

    Art

  2. Hi, David,
    I am trying to use Revision 2 of your Export Outlook to Excel (https://techniclee.wordpress.com/2013/06/21/exporting-appointments-from-outlook-to-excel/ ). I have tried, unsuccessfully so far, to enter the path for one of multiple calendars I would like to use. I have tried all kinds of combinations, including your example: “Mailbox – John Doe\inbox” does not work when I substitute the username for my target Outlook account/calendar. My organization is using Exchange 2007. I would be grateful for your help/clarification/explanation regarding the format for the calendar(s), please. Thanks.

  3. HI David,

    My name is Sarah, i left a msg on the post “Exporting Outlook Messages to Excel”, and got your replies, thank you. But i cannot leave a new comment or just reply to that message, therefore i have to leave it here.

    My outlook is 2007 and would you please advise how i can send the print screen to you.

    Thank you.
    Sarah

    • David,
      It seems like Sarah and I are both asking about the same macro and are both running Exchange 2007….. Please see my separate post with my specific issue/question. Thanks.

  4. Hi David,

    I am a huge huge fan of your work and I am very much thankful to you since just because of your hard work our work time is reduced drastically.

    If you can help me with your email ID that would be of so much of help.

    Mine is: dhruv.somani1@gmail.com

    I need a Macros in which I can have following features:

    1. Folder Selection ability
    2. Date range ability for the macro
    3. Few information form the message context like logon ID User name (first name in one column and last name in one column),job code and job description in separate columns,Application name and email ID in separate column.
    4.Daily a new file according to the date.

    If required I can provide you with the examples on which I will be using this macro.

    Below is such an example of the context of the emails:
    Application : EBG PRODUCTION
    Function Required : Connect

    ***If the action relates to a ‘Connect’, ensure the appropriate ENVIRONMENT Base Job Code already exists before adding a new Job Code for this user***

    JOB CODE DETAILS:-

    Job Code: G-EBGP-ZZDA7GL00
    Job Code Description : IS Display All
    Additional Instructions :
    Business Justification :
    USER DETAILS:-

    PRID: kzgc089
    Logon ID: kzgc089
    User Name: Anandaraj, Pradap (Infosys)
    AD Location: Alderley Park
    Account Type: OSP
    Expiry Date: 2016-11-25
    Phone Number:
    Room Number:
    SMTP Email Address: pradap.anandaraj@astrazeneca.com

  5. Hi David,

    I’m looking to create a macro that searches an alert folder, where the subject includes the string “server reboot pending” , and after the string “site name:” on subject field.

    And on the body field after the string “on these servers:”

    I will export them to a spread sheet every week and update the information with new emails every week.

    Thank you

    • Hi, Davi.

      What information do you want to export for each message? Should the solution create a new spreadsheet each time it’s run or should it update an existing spreadsheet?

  6. Hi David,

    I have another odd situation.

    I am trying to send an Email from a remote PC which I have successfully used to send automated VBA “Ontime” triggered Emails .
    The Emails that I now have a problem with includes two separate bitmap pastes from two separate spreadsheets.

    The Excel macro for the Email is called from an Ontime routine macro that consistently runs on the same remote PC, which works fine – for this purpose as well as others.

    Sub StartTimer()
    Application.OnTime TimeValue(“20:20:00”), “RM008RequestsMTDresults”
    End Sub

    What happens though is that the routine runs OK and the email is sent, however the Email is blank with no bitmap pastes visible.

    If I launch the macro manually from VBA then all is OK and I can watch both bitmap pastes being included n the Email. If I change the routine to include a HTML
    paste as per the ‘For Tips see: http://www.rondebruin.nl/win/winmail/Outlook/tips.htm example, then that works well from the automated VBA Ontime trigger.

    Do you have any suggestions on what the problem might be ?

    …. or what else I could try

    A reduced version of the macro is pasted below

    Sub MTDdailyComposeAndSendEmail() ‘REQUESTSMail_Selection_Range_Outlook_Body()
    ‘For Tips see: http://www.rondebruin.nl/win/winmail/Outlook/tips.htm
    ‘Don’t forget to copy the function RangetoHTML in the module.
    ‘Working in Excel 2000-2013
    Dim rng As range
    Dim OutApp As Object
    Dim OutMail As Object
    Dim myRange As range
    Dim rbnVal As Integer
    Dim incVal As Integer
    Dim NewBook As Workbook
    Dim wrdEdit
    Set oApp = GetObject(, “Outlook.Application”)

    Application.DisplayAlerts = False
    Set wb1 = Workbooks.Open(“D:\NAB New\RM008 Process\Macro Process\Requests Daily Projected MTD Results”)
    Set wb2 = Workbooks.Open(“D:\NAB New\RM008 Process\Macro Process\Requests Oustanding Daily”)
    wb2.Activate
    wb2.Worksheets(“Stats”).Activate
    ‘wb2.Activate

    ‘If DateValue(wb2.Worksheets(“Stats”).Cells(1, “B”)) = DateValue(Now()) Then
    AJOPRemailsent = “yes”
    ‘End If

    Set rng = Sheets(“List4email”).range(“A1:S” & Worksheets(“List4email”).Cells(Rows.Count, “c”).End(xlUp).Row).SpecialCells(xlCellTypeVisible)
    On Error GoTo 0

    Set OutApp = CreateObject(“Outlook.Application”)
    Set OutMail = OutApp.CreateItem(0)
    ‘ OutMail.Display
    On Error Resume Next

    Worksheets(“Stats”).Activate

    wb2.Activate
    wb2.Worksheets(“Stats”).Activate

    x = wb2.Worksheets(“Stats”).Cells(Rows.Count, “C”).End(xlUp).Row

    With OutMail
    .SentOnBehalfOfName = “F0905360@team.telstra.com” ‘modify the “From” field
    ‘.To = “max.p.marcola@team.
    .BCC = “”
    .Subject = “aaaaaaaaaaaaaaaaaaaaaaaaaaaaa”

    Worksheets(“Stats”).range(“A1:AH” & x).CopyPicture Appearance:=xlScreen, Format:=xlBitmap
    Set olkDoc = .GetInspector.WordEditor
    Set wrdApp = olkDoc.Parent
    wrdApp.Selection.HomeKey wdStory
    wrdApp.Selection.PasteSpecial Link:=False, DataType:=wdPasteBitmap, Placement:=wdInLine, DisplayAsIcon:=False

    wb1.Activate
    wb1.Sheets(“Stats”).Activate
    Workbooks(“Requests Daily Projected MTD Results”).Worksheets(“Stats”).range(“A2:I28”).CopyPicture Appearance:=xlScreen, Format:=xlBitmap
    Set olkDoc = .GetInspector.WordEditor
    Set wrdApp = olkDoc.Parent
    wrdApp.Selection.HomeKey wdStory
    wrdApp.Selection.PasteSpecial Link:=False, DataType:=wdPasteBitmap, Placement:=wdInLine, DisplayAsIcon:=False

    .Recipients.ResolveAll
    .Display
    ‘.Send ‘or use .Display
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing

    ‘ActiveWorkbook.Save
    ‘ActiveWorkbook.Close

    wb1.Close
    wb2.Close

    End Sub

    • Hi, Max.

      I believe the issue is that Word won’t perform the paste operation unless the document (message in this case) is visible on screen. Try moving the “.Display” command before the paste.

  7. Hi David,

    Hope you’re well.

    I’ve been struggling with something that I think might be a simple thing. What I want to do is when one particulat type of Email is received I want a bit of code to write a text string into a certain place in a closed Excel spreadsheet – The test string would be simply somthing like cell b3= “File A received”, cell C3 = date. I have kind of done this using some code i got from you some time ago to extract the contents of the body of any Email. Happy to try anything else that you may have in that regard but what I’m now having trouble with is writing those text strings to an Excel file that is open. Are you able to advise on that ?

    Also, I don’yt know if you got my last reply about how to get Excel to insert a blank Email line in the body of an Email in between two bitmap pastes. Appreciate any tips you have on that too.

    Regards
    Max

    • Hi, Max.

      I am well, thanks. I hope you are too.

      The difference between writing to an open Excel file versus a closed one is controlled by how you connect to Excel. When writing to a closed Excel file, you start by creating an instance of Excel, opening a file, and getting a sheet in the workbook. Something like this

      Dim excApp, excWkb, excWks
      'Create an instance of Excel
      Set excApp = CreateObject("Excel.Application")
      'Open an existing workbook
      Set excWkb = excApp.Workbooks.Open("c:\folder\file.xlsx")
      'Get a worksheet from the workbook
      Set excWks = excWkb.Worksheets("Sheet1")
      

      To connect to an open Excel file, the code needs to perform the same general steps, with one important difference. Instead of creating an instance of Excel, the code will connect to a a running instance of Excel and access an open workbook.

      Dim excApp, excWkb, excWks
      'Connect to a running instance of Excel
      Set excApp = GetObject(,"Excel.Application")
      'Get a workbook open in the running instance of Excel
      Set excWkb = excApp.Workbooks("Workbook Name")
      'Get a worksheet from the workbook
      Set excWks = excWkb.Worksheets("Sheet1")
      

      The rest of the code for writing to the worksheet would be the same.

      For the scenario you described (i.e. writing to a sheet when a certain message arrives), you’d want something like the code below. To use this code, you’d create a rule that fires for the messages you want to process. You’d set the rule’s action to “run a script” and select this script as the one to run.

      Sub ProcessMyMessage(olkMsg As Outlook.MailItem)
          'On the next line, set the value to OPEN or CLOSED depending on whether Excel will be open or closed when the macro executes.
          Const EXCEL_WORKBOOK_TYPE = "OPEN"
          'On the next line, edit the path to workbook
          Const WORKBOOK_PATH = "c:\folder\file.xlsx"
          'On the next line, edit the name of the workbook
          Const WORKBOOK_NAME = "Workbook Name"
          'On the next line, edit the name of the sheet within the workbook
          Const WORKSHEET_NAME = "Sheet1"
          Dim excApp As Object, excWkb As Object, excWks As Object
          Select Case EXCEL_WORKBOOK_TYPE
              Case "CLOSED"
                  'Create an instance of Excel
                  Set excApp = CreateObject("Excel.Application")
                  'Open an existing workbook
                  Set excWkb = excApp.Workbooks.Open(WORKBOOK_PATH)
              Case "OPEN"
                  'Connect to a running instance of Excel
                  Set excApp = GetObject(, "Excel.Application")
                  'Get a workbook open in the running instance of Excel
                  Set excWkb = excApp.Workbooks(WORKBOOK_NAME)
          End Select
          'Get a worksheet from the workbook
          Set excWks = excWkb.Worksheets(WORKSHEET_NAME)
          excWks.Cells(3, 2) = "File A received"
          excWks.Cells(3, 3) = Date
          excWkb.Save
          If EXCEL_WORKBOOK_TYPE = "CLOSED" Then
              excWkb.Close False
          End If
          Set excWks = Nothing
          Set excWkb = Nothing
          Set excApp = Nothing
      End Sub
      

      I saw your other question about inserting a blank line between bitmaps. Sorry, I’m behind on things and haven’t gotten to it yet.

  8. Hi David,
    due to corpoarte requirements I cannot send any content however I have replaced data in the workboook with meaningless data fields. As you would expect this has not affected the core issue that I’m pursuing The code I’m experimenting with at the moment appears below (edited a bit to remove some clutter).

    I’ve got a”.Display” in after the first bitmap paste so that it can be checked. The first Bitmap paste is overwritten when ” HTMLBody = REQUESTSRangetoHTML(rng)” Function is completed.

    Now that I’ve prepared this reply I realise that I don’t know how to send you the workbook as I cannot see any way to attach it.

    Apologises… I’m not very familiar with blogs and associated processes. Are you able to advise on that ?

    Sub bbbbbbbbbbbREQUESTSMail_Selection_Range_Outlook_Body()
        Dim rng As range
        Dim rng1 As range
        Dim OutApp As Object
        Dim OutMail As Object
        Dim rgtosendto As String
        Dim tempstr As String
        Dim sendTOstring As String
         Dim isdfound As Integer
          Dim myRange As range
        Dim c As range
        Dim SrchRng
        Dim lastRw As Integer
        Dim FName           As String
        Dim FPath           As String
        Dim NewBook         As Workbook
        'Dim wrdEdit
        'Set oApp = GetObject(, "Outlook.Application")
    
        Set wb2 = Workbooks("TechnicLeeSample")
        wb2.Activate
        wb2.Worksheets("Stats").Activate
        'wb2.Activate
        Set NewBook = Workbooks.Add
        Application.DisplayAlerts = False
        wb2.Sheets("List4email").Copy Before:=NewBook.Sheets(1)
        NewBook.SaveAs FPath & FName  ', AccessMode:=xlExclusive, ConflictResolution:=Excel.XlSaveConflictResolution
        NewBook.Close
        Application.DisplayAlerts = True
        
        Set rng = Nothing
        On Error Resume Next
        
        ActiveWorkbook.Sheets("List4email").Activate
    
        Application.DisplayAlerts = False
        Set rng = Sheets("List4email").range("A1:P" & Worksheets("List4email").Cells(Rows.Count, "c").End(xlUp).Row).SpecialCells(xlCellTypeVisible)
        'Set rng = Sheets("EmailListWS").Range(rng)
        On Error GoTo 0
    
        If rng Is Nothing Then
            MsgBox "The selection is not a range or the sheet is protected" & _
                   vbNewLine & "please correct and try again.", vbOKOnly
            Exit Sub
        End If
    
        With Application
            .EnableEvents = False
            .ScreenUpdating = False
        End With
    
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)
             'OutMail.BodyFormat = olFormatHTML         'PPPPPPPPPPPPPPPPPPPPP
              'OutMail.Display
       
        On Error Resume Next
        
        Worksheets("Stats").Activate
        Worksheets("Stats").range("B2:N53").CopyPicture xlScreen, xlBitmap
        Worksheets("Stats").range("B2:N53").Copy
        
         With OutMail
            .SentOnBehalfOfName = "bbbbb"
            .SentOnBehalfOfName = " field
            .To = "max    
             '.CC = "max
            .BCC = ""
           .Subject = "yyyyyyyyyyyyy"
            
            Set olkDoc = .GetInspector.WordEditor
            Set wrdApp = olkDoc.Parent
            wrdApp.Selection.HomeKey wdStory
            wrdApp.Selection.PasteSpecial Link:=False, DataType:=wdPasteBitmap, Placement:=wdInLine, DisplayAsIcon:=False
            
            .Display
            .HTMLBody = REQUESTSRangetoHTML(rng)
            .Display
            Set olkDoc = .GetInspector.WordEditor
            Set wrdApp = olkDoc.Parent
            wrdApp.Selection.HomeKey wdStory
            wrdApp.Selection.PasteSpecial Link:=False, DataType:=wdPasteBitmap, Placement:=wdInLine, DisplayAsIcon:=False
            .Recipients.ResolveAll  ' does the same as the Check Names button
    
    >
    >
    >
    >
    end sub()
    
    Function REQUESTSRangetoHTML(rng As range)
    ' Changed by Ron de Bruin 28-Oct-2006
    ' Working in Office 2000-2013
        Dim fso As Object
        Dim ts As Object
        Dim TempFile As String
        Dim TempWB As Workbook
    
        TempFile = Environ$("temp") & "\" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"
    
        'Copy the range and create a new workbook to past the data in
        rng.Copy
        Set TempWB = Workbooks.Add(1)
        With TempWB.Sheets(1)
            .Cells(1).PasteSpecial Paste:=8
            .Cells(1).PasteSpecial xlPasteValues, , False, False
            .Cells(1).PasteSpecial xlPasteFormats, , False, False
            .Cells(1).Select
            Application.CutCopyMode = False
            On Error Resume Next
            .DrawingObjects.Visible = True
            .DrawingObjects.Delete
            On Error GoTo 0
        End With
    
        'Publish the sheet to a htm file
        With TempWB.PublishObjects.Add( _
             SourceType:=xlSourceRange, _
             Filename:=TempFile, _
             Sheet:=TempWB.Sheets(1).Name, _
             Source:=TempWB.Sheets(1).UsedRange.Address, _
             HtmlType:=xlHtmlStatic)
            .Publish (True)
        End With
    
        'Read all data from the htm file into RangetoHTML
        Set fso = CreateObject("Scripting.FileSystemObject")
        Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
        REQUESTSRangetoHTML = ts.readall
        ts.Close
        REQUESTSRangetoHTML = Replace(REQUESTSRangetoHTML, "align=center x:publishsource=", _
                              "align=left x:publishsource=")
    
        'Close TempWB
        TempWB.Close savechanges:=False
    
        'Delete the htm file we used in this function
        Kill TempFile
    
        Set ts = Nothing
        Set fso = Nothing
        Set TempWB = Nothing
    End Function
    
    • Max,

      No, there’s no way to attach a file to a post. I’ll email you. You can reply to that email and attach a copy of the workbook.

    • Max,

      For the PasteSpecial to work the message has to be visible onscreen. Move the .Display to before the PasteSpecial.

  9. Hi David, I do appreciate your quck replies …. thanks.

    With the code you sent I have had issues:
    – first up…. at the end of the code the Email only has the contents of ” .HTMLBody = REQUESTSRangetoHTML(Rng)” call, in it.
    – …. on investigating and playing around with that I noticed that when I use the line ” Worksheets(“Stats”).range(“B2:N53″).CopyPicture xlScreen, xlBitmap” and then paused and did a manual paste into a blank email after that, that a blank area was pasted.

    -…. I substituted that with the line ” Worksheets(“Stats”).range(“B2:N53″).Copy” and found that I could successfully manually paste into a blank Email.
    – …. I then changed the order of the Bitmap paste and the HTML paste in the flow of the code and had a “Display ” inbetwen at which I temporarily paused the code . The worksheet Range Bitmap paste was successful but as I stepped onto the HTML paste (” .HTMLBody = REQUESTSRangetoHTML(rng)”) I found that the Bitmap paste had disappeared – I assume that it was pasted over ???

    -… I’ve played around with whatever I could come up with ( which is very little given the low level of my knowledge and experience with this type of thing)

    -….. what I need to achieve is the worksheet Range Bitmap paste at the top of the Email followed a line or twobelow by the HTML paste ( can be done manually)

    -….. Are you able to advise further on this ?

    The code sort of ended up being……………….

    Set OutApp = CreateObject(“Outlook.Application”)
    Set OutMail = OutApp.CreateItem(0)

    Worksheets(“Stats”).Activate
    ”””Worksheets(“Stats”).range(“B2:N53”).CopyPicture xlScreen, xlBitmap
    Worksheets(“Stats”).range(“B2:N53”).Copy

    With OutMail
    .SentOnBehalfOfName = “bbbbb”
    .SentOnBehalfOfName = “xxxx” ‘modify the “From” field
    .To = “max.p.marcola@t……….” ‘
    .BCC = “”
    .Subject = “yyyyyyyyyyyyy”
    .””””””””’Attachments.Add FPath & FName

    Set olkDoc = .GetInspector.WordEditor
    Set wrdApp = olkDoc.Parent
    wrdApp.Selection.HomeKey wdStory
    wrdApp.Selection.PasteSpecial Link:=False, DataType:=wdPasteBitmap,
    Placement:=wdInLine, DisplayAsIcon:=False
    .Display
    .HTMLBody = REQUESTSRangetoHTML(rng)
    .Display

    .Recipients.ResolveAll ‘ does the same as the Check Names button

    • Hi, Max.

      Could you send me a copy of the workbook you’re using? That would make it a lot easier to troubleshoot the issue.

    • Hi David, Thanks, this worked well and I’ve been playing around with it a bit.
      One thing that is really bugging me at the moment is that I cannot seem to insert a blank line in the Email body between the HTML paste and the Bitmap paste or between sequential multiple Bitmap pastes without overwriting what was already in the body.
      Any advice on that ?

    • Hi, Max.

      My deepest apologies for taking so long to get back to you on this.

      Add this command

      wrdApp.Selection.InsertAfter vbCrLf & vbCrLf
      

      after pasting in the graphic. In the context of your code

      With OutMail
          .SentOnBehalfOfName = "bbbbb"
          .To = "max.p.marcola@xxxxxxxx"
          '.CC = ""
          '.BCC = ""
          .Subject = "yyyyyyyyyyyyy"
          .Attachments.Add FPath & FName
          .HTMLBody = REQUESTSRangetoHTML(Rng)
          .Display
          Set olkDoc = .GetInspector.WordEditor
          Set wrdApp = olkDoc.Parent
          wrdApp.Selection.HomeKey wdStory
          wrdApp.Selection.PasteSpecial Link:=False, DataType:=wdPasteBitmap, Placement:=wdInLine, DisplayAsIcon:=False
          wrdApp.Selection.InsertAfter vbCrLf & vbCrLf
      
          .Recipients.ResolveAll  ' does the same as the Check Names button
      
          'Application.Wait (Now + TimeValue("0:00:15"))
          '.Send   'or use .Display
      End With
      
  10. Hi David, I want to copy range from a worksheet. The range will have set columns and always start at a set row. The end row will vary but I’m now familiar with how to specify and select a variable range like that. It’s the copy and paste as a bitmap that’s got me stumped.

    The overall process runs on Office 2007.

    Thanks
    Max

    • Hi, Max.

      Add this line of code to copy the range as a bitmap. Edit the range as needed.

      Worksheet.Range("A1:D4").CopyPicture xlScreen, xlBitmap
      

      Now, add a reference to the “Microsoft Word 12.0 Object Library”. Finally, to paste the picture into email, replace the block of code that creates the email with this version

          With OutMail
              .SentOnBehalfOfName = "bbbbb"
              .To = "max.p.marcola@xxxxxxxx"
              '.CC = ""
              '.BCC = ""
              .Subject = "yyyyyyyyyyyyy"
              .Attachments.Add FPath & FName
              .HTMLBody = REQUESTSRangetoHTML(Rng)
              .Display
              Set olkDoc = .GetInspector.WordEditor
              Set wrdApp = olkDoc.Parent
              wrdApp.Selection.HomeKey wdStory
              wrdApp.Selection.PasteSpecial Link:=False, DataType:=wdPasteBitmap, Placement:=wdInLine, DisplayAsIcon:=False
              .Recipients.ResolveAll  ' does the same as the Check Names button
       
              'Application.Wait (Now + TimeValue("0:00:15"))
              '.Send   'or use .Display
          End With
      
  11. Hi David, hope you’re well.
    You helped me with some Outlook vba code about 18 months ago and that’s working well. Currently I’m a little stuck with trying to send an Email from Excel with a bitmap image of an Excel range preceeding a HTML range paste ( a Ron de Bruin bit of code I got from you? back then) in the Email
    What I need is a bit of code that will do the equivalent of Cnt C (copy) and Cnt V (paste) as bit map from another worksheet into the Email prior to the .HTMLBody = REQUESTSRangetoHTML(rng) data.

    Do you have anything available along those lines ?

    Regards
    Max

    Set rng = Sheets("List4email").range("A1:S" & Worksheets("List4email").Cells(Rows.Count, "c").End(xlUp).Row).SpecialCells(xlCellTypeVisible)
        
          On Error GoTo 0
    
        If rng Is Nothing Then
            MsgBox "The selection is not a range or the sheet is protected" & _
                   vbNewLine & "please correct and try again.", vbOKOnly
            Exit Sub
        End If
    
        With Application
            .EnableEvents = False
            .ScreenUpdating = False
        End With
    
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)
        
        On Error Resume Next
        With OutMail
            '.To = "max.p.marcola@xxxxxxxxx
            .SentOnBehalfOfName = "bbbbb" field
            .To = "max.p.marcola@xxxxxxxx" '& ";"         .BCC = ""
            .Subject = "yyyyyyyyyyyyy
            
    
           .HTMLBody = REQUESTSRangetoHTML(rng)
            .Attachments.Add FPath & FName
            .Display
            .Recipients.ResolveAll  ' does the same as the Check Names button
    
    '        Application.Wait (Now + TimeValue("0:00:15"))
            '.Send   'or use .Display
        End With
        On Error GoTo 0
    
    
        With Application
            .EnableEvents = True
            .ScreenUpdating = True
        End With
    
        Set OutMail = Nothing
        Set OutApp = Nothing
        
        Cells(1, 1).Select
       'ActiveWorkbook.Save
       'ActiveWorkbook.Close
        
       Application.DisplayAlerts = True
    End Sub
    
    ,................................
    Function REQUESTSRangetoHTML(rng As range)
    ' Changed by Ron de Bruin 28-Oct-2006
    ' Working in Office 2000-2013
        Dim fso As Object
        Dim ts As Object
        Dim TempFile As String
        Dim TempWB As Workbook
    
        TempFile = Environ$("temp") & "\" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"
    
        'Copy the range and create a new workbook to past the data in
        rng.Copy
        Set TempWB = Workbooks.Add(1)
        With TempWB.Sheets(1)
            .Cells(1).PasteSpecial Paste:=8
            .Cells(1).PasteSpecial xlPasteValues, , False, False
            .Cells(1).PasteSpecial xlPasteFormats, , False, False
            .Cells(1).Select
            Application.CutCopyMode = False
            On Error Resume Next
            .DrawingObjects.Visible = True
            .DrawingObjects.Delete
            On Error GoTo 0
        End With
    
        'Publish the sheet to a htm file
        With TempWB.PublishObjects.Add( _
             SourceType:=xlSourceRange, _
             Filename:=TempFile, _
             Sheet:=TempWB.Sheets(1).Name, _
             Source:=TempWB.Sheets(1).UsedRange.Address, _
             HtmlType:=xlHtmlStatic)
            .Publish (True)
        End With
    
        'Read all data from the htm file into RangetoHTML
        Set fso = CreateObject("Scripting.FileSystemObject")
        Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
        REQUESTSRangetoHTML = ts.readall
        ts.Close
        REQUESTSRangetoHTML = Replace(REQUESTSRangetoHTML, "align=center x:publishsource=", _
                              "align=left x:publishsource=")
    
        'Close TempWB
        TempWB.Close savechanges:=False
    
        'Delete the htm file we used in this function
        Kill TempFile
    
        Set ts = Nothing
        Set fso = Nothing
        Set TempWB = Nothing
    End Function
    
    • Hi, Max.

      I am doing well, thanks. It’s good to hear from you again.

      What is it that you want to copy? The entire sheet, or just a potion of it? If it’s the latter, then what portion? Also, what version of Office are you using?

  12. Hello,
    At the beginning sorry for my poor English, I’m from Poland.
    Please help in the macro.
    I have a shared folder in Outlook contacts (Exchange) under the name “Shared”, in which I have written numbers (for example) 0601123123.
    In addition, I have folders with contacts under the names “Contacts” and “Jabber contacts”
    Needs to folder “Jabber contacts” were copied all the contacts from the “Shared” and the folder “Contacts” have copied all the contacts, but the change in the number of “0601123123” to “+48601123123”.
    In addition, each contact has a photo.
    Can I ask in this topic, I do not know how to go about this.

    • Witaj, Daniel.

      Przykro mi, ale nie rozumiem, co chcesz zrobić. Proszę napisać swój wniosek w języku polskim i będą korzystać z Google Translate, aby przekształcić go w języku angielskim. To powinno ułatwić, aby wyjaśnić, co chcesz makro robić.

    • Mam w Outlook’u udostępnione kontakty nazwane jako “Shared”.
      Kontakty w tym folderze mam zapisane jako, przykład 0601123123 (pierwsze 0)
      Mam dwa foldery kontaktów lokalnych nazwane jako “Kontakty” oraz “Jabber kontakty”.
      Potrzebuje makro, które bedzie kopiować wszyskie kontakty z folderu “Shared”:
      – do “Jabber kontakty” (tutaj bez żadnych zmian)
      – do folderu “Kontakty” (tutaj każdy numer musi zmienić pierwszą cyfrę “0” na “+48”
      Dodatkowo każdy kontakt zawiera zdjęcie (nie wiem czy ma to wpływ).

  13. Hello David,

    I’m in an insurance company and once a claim is lodged into the system a system generated message gets sent to investigator however at times due to system glitch message doesn’t get delivered. To identify the particular case I’ve to ask team to check all the messages manually.

    If possible I want a macro to be run on all the sent emails wherein claim number of the case from the body of the email can be extract and can saved in excel so that at the end of the day I can filter this and can identify that which email was not delivered.

    String to be extracted from the email body – “Claim No: 11000/31/15/C/0197471”

    Email body contains details as below….

    “You been appointed as a Surveyor/investigator/advocate for the claim, details are mentioned below Surveyor Type: Final,Policy No: 123456/31/15/001823,Claim No: 11000/31/15/C/0197471,Claim Loss Date :17-SEP-14,Vehicle No:NEW 17E 15 7007,Insured Name”

    Please suggest.

    Thanks and regards,
    S.K Sharma

    • Hi, S.K Sharma.

      That’s easily done. Do you want each export written to a new spreadsheet or appended to an existing spreadsheet? Also, do you want to limit the export to a date range (e.g. just those messages sent today) to avoid exporting the same messages over and over?

  14. Here I am back with a different question given your amazing efficiency David!

    I am using the below macro to split a sheet in excel into several worksheets based on a specific column (column 4 in my case below)… my issue is that the new worksheets are not linked to the main database sheet and hence the changes I make in the main datasheet are not reflected in the newly created sheet… is there anyway the macro could keep such a link?

    Thanks much!

    Option Explicit
    
    Sub ParseItems()
    'Jerry Beaucaire  (11/11/2009)
    'Based on selected column, data is filtered to individual sheets
    'Creates sheets and sorts sheets alphabetically in workbook
    '6/10/2010 - added check to abort if only one value in vCol
    Dim LR As Long, Itm As Long, MyCount As Long, vCol As Long
    Dim WS As Worksheet, MyArr As Variant, vTitles As String, Oops As Boolean
    
    Application.ScreenUpdating = False
    
    'Column to evaluate from, column A = 1, B = 2, etc.
       vCol = 4
    
    'Sheet with data in it, change to suit
       Set WS = Sheets("Data")
    
    'Range where titles are across top of data, as string, data MUST
    'have titles in this row, edit to suit your titles locale
        vTitles = "A1:E1"
       
    'Spot bottom row of data
       LR = WS.Cells(WS.Rows.count, vCol).End(xlUp).Row
    
    'Get a temporary list of unique values from column A
        WS.Columns(vCol).SpecialCells(xlConstants).AdvancedFilter Action:=xlFilterCopy, _
            CopyToRange:=WS.Range("EE1"), Unique:=True
    
    'Sort the temporary list
        WS.Columns("EE:EE").Sort Key1:=WS.Range("EE2"), _
            Order1:=xlAscending, Header:=xlYes, OrderCustom:=1, _
            MatchCase:=False, Orientation:=xlTopToBottom, _
            DataOption1:=xlSortNormal
    
    'Check for more than one value in list
        If WS.Range("EE" & Rows.count).End(xlUp).Row > 2 Then
    
    'Put list into an array for looping
    '(values cannot be the result of formulas, must be constants)
            MyArr = Application.WorksheetFunction.Transpose(WS.Range("EE2:EE" _
                & Rows.count).SpecialCells(xlCellTypeConstants))
    
    'clear temporary worksheet list
            WS.Range("EE:EE").Clear
    
        Else
            WS.Range("EE:EE").Clear
            Oops = True
            GoTo ErrorExit
        End If
        
    'Turn on the autofilter, one column only is all that is needed
        WS.Range(vTitles).AutoFilter
    
    'Loop through list one value at a time
        For Itm = 1 To UBound(MyArr)
            WS.Range(vTitles).AutoFilter Field:=vCol, Criteria1:=MyArr(Itm)
        
            If Not Evaluate("=ISREF('" & MyArr(Itm) & "'!A1)") Then    'create sheet if needed
                Worksheets.Add(After:=Worksheets(Worksheets.count)).Name = MyArr(Itm)
            Else                                                      'clear sheet if it exists
                Sheets(MyArr(Itm)).Move After:=Sheets(Sheets.count)
                Sheets(MyArr(Itm)).Cells.Clear
            End If
    
        'customize this section as needed for copy/paste targets
            WS.Range("A" & WS.Range(vTitles).Resize(1, 1).Row & ":A" & LR) _
                .EntireRow.Copy Sheets(MyArr(Itm) & "").Range("A1")
    
            
            WS.Range(vTitles).AutoFilter Field:=vCol
            MyCount = MyCount + Sheets(MyArr(Itm)) _
                .Range("A" & Rows.count).End(xlUp).Row - 1
            Sheets(MyArr(Itm)).Columns.AutoFit
        Next Itm
        
    'Cleanup
        WS.AutoFilterMode = False
        MsgBox "Rows with data: " & (LR - 1) & vbLf & "Rows copied to other sheets: " _
                    & MyCount & vbLf & "Hope they match!!"
    
    ErrorExit:
        If Oops Then MsgBox "Only one value found, aborting parse process..."
        Application.ScreenUpdating = True
    End Sub
    
    • Rana,

      I looked through the code, tested the code, and re-read your message. Are you saying that you want the data copied to each individual sheet to be linked to the main sheet in such a way that any change you make to the data in the main sheet will be reflected in each of the individual sheets?

    • Rana,

      Does each row of data in your main sheet contain some unique value that could be used as a key? If not, would you be willing to add a column for a key value? I ask because the only way I can think of to make this work is to trap changes, see which row is changed, then look up the matching value in the individual sheets and update that value. Doing that requires a key to ensure that the process updates the correct row in the individual sheet.

    • Hi David,

      If I got you right, the data value in 2 columns together (being first and last name) can be considered as unique value… does this answer your question?

      Cheers!

    • Rana,

      I can use that if you want, but names are rarely suitable as unique values. There’s always the possibility of two people with the same name. An employee ID number would be a better choice.

      Would a solution need to be capable of handling changes to the field the data is parsed on which would cause a value to need to move from one sheet to another?

    • I see.. you’re right… Actually, yes I can use the passport number as unique value….
      and yes indeed; there are some instances where the data needs to be moved to a different sheet, and some other instances where not…

    • Rana,

      Ok. I may have thought of a better solution. I’ll test that thought and be in touch once I know more.

    • Rana,

      Does the data have to be split out into separate sheets? Why not turn the data into a table and use the table’s built-in filtering capability to restrict your view of the data to just those rows that meet some filtering criteria?

    • Hi David,

      yes data have to be split out into separate sheets (by moving entire rows) – and the filtering option, followed by a simple copy/paste can definitely help… the issue is with the changes made on the main database that will not not be reflected automatically in the “sub-sheets” which I was actually trying to do….

      Best
      Rana

    • Rana,

      I understand what you want. I’m curious as to why. Why does the data needs to be on separate sheets? If the data can stay on one sheet yet can be filtered to achieve the same result as splitting it out into separate sheets, then why go to all the work of making separate sheets and having to invent some way to link the data from each sheet back to the master sheet?

    • I see now what you mean…. it’s because each sheet will have to be dispatched to a different user with specific formatting grouping rows together (depending on specific criteria) and where this same formatting will not be reflected in the main data base… so while the changes made in the main database are to be reflected in the rest of the sheets, the changes made to the formatting in the separate worksheets will not show in the main database…

  15. hi David
    hope your well, as the guru I have come back again for your assistance.
    I’m trying to create a shared calendar that will host a group of users calendar entries, e.g. the list of users who receive a calendar invite or they enter a calendar appointment in to their calendar then that appointment should also be copied or added to the shared calendar.
    is this possible with a simple script or central script that look at the group of users calendar and adds any new entries to a central calendar.

    Any help or advise would be greatly appreciated as always.
    thanks
    R

    • Hi, R.

      Good to hear from you again.

      Yes, that’s doable. There might be a better solution, but I’d need to know more about your environment and what the goal here is. If you just need a way to see everyone’s appointments, then it would be better to build a team calendar (i.e. overlay all the calendars into a virtual calendar). That aside, if you go the script route and more than one person in the group is invited to the same meeting, then you’re going to end up with duplicate entries. You’re also going to need a way to tell which entry is from which person.

      Let me know how you’d like to proceed and we’ll take it from there.

    • Hi David
      we did try to overlay all the calendars into a virtual calendar which was quite a mess, as you say the multiple entries from the same individuals invited created duplicate entries and the calender view was overloaded.

      the script route i was thing if possible is to place a script some where centrally that looks at the group of individuals and copy’s their calendar entries to a central shred one, the script should look at the subject field if exist already then it doesn’t add it but if not exist then add to central shared calendar.

      we are ruining exchange 2010 with office 2010 on windows 7 0platforms.
      thanks again
      R

    • Hey, R.

      For a centralized solution to work, you’ll have to install Outlook on the central computer and either create a mail profile that has access to all the user’s accounts or have those users share their calendar with the central account so it can access them. Anytime the group adds or loses a member someone will have to edit the script and add/remove the account from the list of accounts it’s checking. The alternative is to install the script on each team member’s computer and have it run at some interval. Doing it this way would save you from having to share accounts or set permissions on the calendars, as well as eliminating the need to edit the list of mailboxes. Any computer with the script would sync with the central calendar. You could even place the script on a network share that all computers have access to rather than having to install the script on each computer. All you’d need to do is point the scheduled task to the script.

      The script will need to be written in VBscript so you can schedule it using Windows Task Scheduler to run at some interval (e.g. once an hour). To avoid duplicates, the script should look at both the subject and appointment time, not just the subject. The problem with looking at the subject alone is that multiple appointments might have the same subject but be scheduled for different times.

      Let me know which approach you want to take. I can also provide a script that will create the scheduled task.

    • David you’re a legend that’s exactly the kind of script i’m looking for and i hope its not too-much of a bother for you, as always this is much appreciated.

    • Hey, R.

      Sorry to have taken so long to get back to you on this.

      The code is nearly ready. I’ve just one question I need answered in order to finish it. Do you want just the person’s appointments, or do you want their meetings too? The difference between an appointment and a meeting is that appointments don’t have any invitees whereas meetings do. Here’s why I’m asking. Let’s say you deploy the solution to a team of 20 people. Now, let’s say that someone creates a meeting inviting all 20 of those people to attend. When the sync runs that’s going to create 20 copies of the same meeting on the central calendar. That’s going to make the central calendar very difficult to read.

    • Hi David,
      Not a problem I’m sure you’re a busy dude working on multiple things, if it’s not a big change or too much work is it possible to have both but with the option to comment out meetings after I test and see what it looks like, otherwise appointments only will be fine.
      One other question is opposable to exclude any appointments marked as private, again if the option or ability to comment out the private would be helpful.
      Thanks,
      R

    • Good morning, R.

      Here’s my solution. Here’s how it works.

      1. Open Outlook in the background
      2. Connect to the user’s calendar
      3. Connect to the central calendar
      4. Delete all the user’s appointments from the central calendar. It does this because the alternative would be to try to figure out which appointments have been added, which have been changed, and which have been deleted. By deleting all the existing appointments at the outset all the script has to do is copy the appointments from the user’s calendar.
      5. Filter the user’s calendar to include just those appointments that fall within the next x days (the number of days specified by the DAYS_TO_PUBLISH value in the script). The solution will not copy appointments that are marked “Private”.
      6. Copy the filtered appointments to the central calendar

      The solution only copies appointments, not meetings. The difference between an appointment and a meeting is that an appointment is just for the user whereas a meeting has other attendees. The problem with copying meetings is that doing so may make the central calendar very difficult to read. The solution appends the user’s name to the subject of each copied appointment. That will make it easy to know who owns each appointment on the central calendar.

      To use the solution

      1. Copy the script and paste it into Notepad
      2. Edit the script per the comments I included in it. You will need to set the path to the central calendar and edit the number of days of information to copy.
      3. Save the file. You can name it anything you want. The file extension must be .vbs.
      4. You can run the sync manually by double-clicking the file.
      5. To schedule the script, create a new task in Windows Task Scheduler. Set the task to run at whatever interval you desire. Set the task to run this script.

      Please try the solution out and let me know if that’s what you wanted.

      '--> Declare some constants
      'On the next line, edit the path to the central calendar
      Const TGT_CAL = "Path_to_Central_Calendar"
      'On the next line, edit the number of days of information to sync to the central calendar
      Const DAYS_TO_PUBLISH = 7
      Const olNonMeeting = 0
      Const olPrivate = 2
      Const olFolderCalendar = 9
      
      '--> Declare some variables
      Dim olkApp, olkSes, olkSrc, olkTgt, olkFlt, olkApt, olkCpy, olkMov, datBeg, datEnd, lngCnt, strF1, strF2
      
      '--> Initialize some variables
      datBeg = Now
      datEnd = DateAdd("d",DAYS_TO_PUBLISH,datBeg)
      lngCnt = 0
      strF1 = "[Start] >= '" & OutlookDateFormat(datBeg) & "' AND [Start] <= '" & OutlookDateFormat(datEnd) & "'"
      strF2 = "@SQL=" & Chr(34) & "http://schemas.microsoft.com/mapi/proptag/0x0037001E" & Chr(34) & " like '%NAME%'"
      
      '--> Connect to Outlook
      Set olkApp = CreateObject("Outlook.Application")
      Set olkSes = olkApp.GetNamespace("MAPI")
      olkSes.Logon olkApp.DefaultProfileName
      
      '--> Connect to the source and target calendars
      Set olkSrc = olkApp.Session.GetDefaultFolder(olFolderCalendar).Items
      Set olkTgt = OpenOutlookFolder(TGT_CAL)
      
      '--> Filter the target calendar by the current user and delete all of their appointments
      strF2 = Replace(strF2,"%NAME%","%[" & olkSes.CurrentUser.Name & "]%")
      Set olkFlt = olkTgt.Items.Restrict(strF2)
      For lngCnt = olkFlt.Count To 1 Step -1
      	Set olkApt = olkFlt.item(lngCnt)
      	olkApt.delete
      Next
      
      '--> Filter the source calendar to only return the appointments that begin within the next DAYS_TO_PUBLISH days
      olkSrc.Sort "[Start]", False
      olkSrc.IncludeRecurrences = True
      Set olkFlt = olkSrc.Restrict(strF1)
      
      '--> Add the appointments from the filtered source calendar to the target calendar
      For Each olkApt In olkFlt
      	If olkApt.MeetingStatus = olNonMeeting Then
      		If (Not olkApt.AllDayEvent) And (Not olkApt.Sensitivity = olPrivate) Then
      			Set olkCpy = olkApt.Copy
      			Set olkMov = olkCpy.Move(olkTgt)
      			olkMov.Subject = "[" & olkSes.CurrentUser.Name & "] " & olkMov.Subject
      			olkMov.Save
      		End If
      	End If
      Next
      
      '--> Disconnect from Outlook
      olkSes.Logoff
      
      '--> Clean-up
      Set olkMov = Nothing
      Set olkCpy = Nothing
      Set olkApt = Nothing
      Set olkFlt = Nothing
      Set olkTgt = Nothing
      Set olkSrc = Nothing
      Set olkSes = Nothing
      Set olkApp = Nothing
      
      '--> End script
      WScript.Quit
      
      Function OpenOutlookFolder(strFolderPath)
          Dim arrFolders, varFolder, bolBeyondRoot
          On Error Resume Next
          If strFolderPath = "" Then
              Set OpenOutlookFolder = Nothing
          Else
              Do While Left(strFolderPath, 1) = "\"
                  strFolderPath = Right(strFolderPath, Len(strFolderPath) - 1)
              Loop
              arrFolders = Split(strFolderPath, "\")
              For Each varFolder In arrFolders
                  Select Case bolBeyondRoot
                      Case False
                          Set OpenOutlookFolder = olkSes.Folders(varFolder)
                          bolBeyondRoot = True
                      Case True
                          Set OpenOutlookFolder = OpenOutlookFolder.Folders(varFolder)
                  End Select
                  If Err.Number <> 0 Then
                      Set OpenOutlookFolder = Nothing
                      Exit For
                  End If
              Next
          End If
          On Error GoTo 0
      End Function
      
      Function OutlookDateFormat(varDate)
          Dim intHour, strAMPM
          intHour = Hour(varDate)
          If intHour > 12 Then
              intHour = intHour - 12
              strAMPM = "PM"
          Else
              strAMPM = "AM"
          End If
          OutlookDateFormat = Month(varDate) & "/" & Day(varDate) & "/" & Year(varDate) & " " & intHour & ":" & Minute(varDate) & " " & strAMPM
      End Function
      
  16. Hi David,

    Do you know a way to send automatic confirmation email every time you receive an email on Outlook? I am using the rules function of Outlook and a customized template. Unfortunately, the rule seems to deliver a confirmation email only once during a session. I receive a lot of emails a day and sometime from the same sender and it is important for me to let the sender knows that his email is being processed.
    Thank you for any assistance you could provide.

    • Hi, Guy.

      There is another solution, one using a macro, but the rule ought to work. Can you share the rule you have set up now?

  17. Hi Lee. Is there any way at all of making a recurring task or event in Outlook that notifies on an hourly basis? I am trying to get reminders hourly to do the same thing. Thanks.

  18. Hi david
    In outlook everyday i get request from 4 diffrent persons to order the product and sometimes the requests are duplicate.Sometime they send the request in the body and sometime as an attachment.I can make them do ethier of the one.Can you provide me with the code that takes the data to the excel file at the desktop with the requested time.and eliminates the dup. records and the macro should run automatically as and when the mail arrives at the folder.The table would be like:
    TAG,ITEM NO,LOCATION,QTY, QTY REQUIRED,TIME OF REQUEST,STATUS(WHICH IS UPDATED MANUALLY)
    The data should be on the same sheet everyday and nothing should be deleted like the previous requests and the new request comes at the top/bottom of the sheet.and sometime there are 5 to 7 request from the same person on the same mail thread.I can make the rule for the request to go to the specific folder but can not break the thread or tell them to give new mail each time.please help
    THANKS DAVID

    • Hi, Vikrant Rana.

      Can you share a sample message/file with me so I can see how it’s laid out?

    • here is a sample”These are the fields what i get in the mail
      Tag| Item| Description 1| Location| Curr Qty| Unfilled pcs| Warehouse

    • Hi, Vikrant Rana.

      That doesn’t help. I really need to have a sample of an actual message so I can see how it’s laid out.

    • Hi David, Sorry for the late reply I was out of country here is the sample

      *From:* Jennifer Coombs *Sent:* May-09-14 11:55 AM *To:* Sandra Adams; Hamid Mian; Mike MacDonald *Subject:* Please store

      Hi,

      Please have the below stored for Defective RA. Thanks

      A6652266

      BRDNNVG294180

      FROM UP ON POPPY HILL(JPN/W/DVD/ENG DUB+

      410.000

      1.000

      0.000

      0.000

      6.000

      1.000

      3.000

      TOR6677

      DMG

      A6023479

      DVDNNVG283530

      MAGIC SCHOOL BUS, ALL ABOUT EARTH (ENG->

      TODBULK02

      785.000

      30.000

      0.000

      0.000

      37.000

      30.000

      DOCK

      DMG

      A6657707

      DVDNNVG292610

      MAGIC SCHOOL BUS-SPACE ADVENTURES

      23.000

      1.000

      0.000

      0.000

      17.000

      1.000

      TOR2919

      DMG

    • Vikrant,

      I need a copy of an actual message, not a copy and paste. A copy and paste can lose some of the source formatting. Can you send me an email with a sample message attached?

  19. Hello David, I have been reading your site and am impressed. I have Outlook 2010 on a PC running Win 7 64 bit. I am looking for software that can convert outlook appointments into bills. There are several that I have found but none run on my 64 bit pc. Do you have a script that could export groups of appointments to Excel? I do not want my whole calendar, but I do want data from multiple pst files, since I keep my pst files small through archiving and compacting. I also have a technet subscription and am an MCSE if you would rather me contact you through MS. Thanks, Phil

    • Hi, Phil.

      Thanks!

      I’ll help if I can. This forum is fine for contacting me. Can you share more detail about how you need a solution to work? For example, do you need the solution to export from one calendar at a time or from multiple calendars at once? What is the selection criteria for determining which appointments to export? What information do you want from each appointment? Does the solution need to create a new spreadsheet each time or should it update an existing spreadsheet? If it’s the latter, does the solution need to find an update entries in the spreadsheet or simply append newly exported items to it?

    • David, thank you for responding to my question. I would need to solution to export from multiple calendars, but only one at a time. The search criteria would be the subject of each appointment. I would like to capture the subject, the location, the start date, end date, start time, end time and duration as separate fields if possible. The solution would create a new spreadsheet each time it runs. Your help is greatly appreciated!

    • Hi, Phil.

      I’ve added a new revision (Rev 3) containing your solution to my post titled “Exporting Appointments from Outlook to Excel”. Please try that solution and let me know if that’s what you wanted.

  20. David,
    I am trying to extract information from outlook emails into an excel spreadsheet. Each email is exactly the same. They have all been automatically been put into a folder. Each email information needs to be on its own row. This needs to be run manually once a day, making it own daily spreadsheet then saving it. I have looked at an earlier post at

    https://techniclee.wordpress.com/2011/10/29/exporting-outlook-messages-to-excel/

    When I insert the code into outlook, I get a compile error: syntax error, and it highlights the top eight rows of code.

    Any help would be greatly appreciated.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s