Personally I do not like the way that Microsoft Outlook prints out meetings especially the fact that attendees are a comma separated list.
I prefer to see all the attendees as a tabular list – with their acceptance response so that I can tick them off as they join the meeting.
The following macro will do this for you.
NOTE 1 : this macro is amending the text of the body of the meeting invite, if you save this and send an update – everyone will see this. I prefer to just run the macro, print the meeting and then close without saving.
NOTE 2 : this is a one off snapshot of the attendee status, if further responses or updates are received you will have to delete the old text then re-run the macro.
Continue reading Outlook Meeting Attendees In The Invite Body
There are times when you have an excel sheet which has multiple lines of text in a single cell, which has been split using a carriage return. NOTE: I’m not talking about text which has wrapped due to the size or formatting of the cell.
If you need to separate the contents of this single cell, into one row per line then this is the macro for you. NOTE: This macro will insert rows into your sheet so you may have to “fix” the layout afterwards. Save your sheet before you run this just in case.
The first function processes the current cell – use this if you only have one cell which you want to split.
Public Sub SplitCellToRows()
arrValues = Split(ActiveCell.Value, vbLf)
For i = UBound(arrValues) To LBound(arrValues) Step -1
'MsgBox i & " " & arrValues(i)
If i > 0 Then
ActiveCell.Offset(Sgn(i)).Value = arrValues(i)
If you have multiple cells which you want to split out then there is a wrapper macro which will call this multiple times.
Public Sub SplitCellToRows_Multiple()
For Each cell In ActiveCell.CurrentRegion.Cells
To use, simply highlight one or more cells and then run the appropriate macro.
There are times when you are using an Excel workbook and you simply want to search for some content but on one of the tables on one of the sheets the table which contains the data has been filtered. You can spend more time looking for and then removing the filter than you do in running the actual search.
This macro will remove all filters from your current workbook.
Continue reading Excel – Unfilter all sheets
A commenter on one of my other macros requested this functionality.
Whenever you send an e-mail in Outlook have it prompt you if you want it filed in a folder other than the default Sent Items folder.
Here it is.
I’m assuming that you are using the default Sent Items folder.
You will have to edit the code to point it to the folder you wish to use.
First you have to define the event handler to monitor your sent items folder.
Private WithEvents SentItems As Outlook.Items
Private Sub Application_Startup()
Dim NS As Outlook.NameSpace
Set NS = Application.GetNamespace("MAPI")
Set SentItems = NS.GetDefaultFolder(olFolderSentMail).Items
Now you have to add a routine to handle it.
Private Sub SentItems_ItemAdd(ByVal item As Object)
Dim objMailItem As mailItem
Set objMailItem = item
Dim arcFolder As Outlook.MAPIFolder
Set arcFolder = Outlook.Application.Session.Folders.item("Personal Folders (C)").Folders.item("___ToDo")
If MsgBox("Move To ToDo?", vbYesNo) = vbYes Then
I have a “ToDo” folder in my Personal Folders mailbox (the leading underscores are simply to “help” the sorting in the default view.
It’s this arcFolder location that you will have to adjust to your own required location.
I’m looking for some help.
I have a variety of Outlook macros (see here) that run automatically when I send an e-mail. Recently these have stopped working and I see no errors.
If I manually trigger the same macro then it works and from that point onwards it will run automatically.
I need to produce a weekly report detailing what work I have carried out. I diligently record this in my Outlook calendar. My weekly report needs to be submitted in Microsoft Excel.
The following macro will pull the entries for the last seven days from my calendar and store it in Excel format.
Continue reading Outlook Calendar To Excel
How often are you in Outlook and you receive an e-mail from someone and you want to know a little bit more about them. It may be as simple as looking up their phone number so that you can get back to them.
This lookup may be on your own intranet site or using LinkedIn, Facebook or Twiter.
The following Outlook macro will allow you to do this.
Continue reading Outlook Macro – Lookup
One of the main reasons that I use Microsoft Excel is to keep lists to share with multiple people. Microsoft Word has great versioning and colabarative editing capabilities, Excel not so much. The main problem I have is knowing when a particular row has been changed and who changed it.
The following macro implements this.
Continue reading Track Changes In Excel
I have just finished reviewing an excel spreadsheet where a number of the rows were formatted with strikethough :
The formatting was valid and the rows have to stay there – but I have no need to review those rows. In excel you can filter rows, by contents or even by colour but not by format.
Enter a VBA user function:
Continue reading Filter Cells In Excel With Strike Through Formatting
I commonly have large excel spreadsheets with many tabs or worksheets within the same workbook.
I use the following Microsoft excel macro to generate a contents page.
NOTE: I’m assuming the “Contents” worksheet will be the first and the list will be generated from A2 downwards.
<span style="color: #222222;">Private Sub CommandButton1_Click()</span><br style="color: #222222;" /><span style="color: #222222;"> For i = 2 To Sheets.Count</span><br style="color: #222222;" /><span style="color: #222222;"> Range("a" & i) = Sheets(i).Name</span><br style="color: #222222;" /><span style="color: #222222;"> ActiveSheet.Hyperlinks.Add Anchor:=Range("a" & i), Address:="", SubAddress:="'" & Sheets(i).Name & "'!A1", TextToDisplay:=Sheets(i).Name</span><br style="color: #222222;" /><span style="color: #222222;"> Next i</span><br style="color: #222222;" /><span style="color: #222222;"> Columns("A:A").EntireColumn.</span><wbr style="color: #222222;" /><span style="color: #222222;">AutoFit</span><br style="color: #222222;" /><span style="color: #222222;">End Sub</span>