How I use Mail Merge for name badge labels using Word (& Excel or Notepad)

This is not a general ‘how to use Mail Merge’ post, it’s mostly a reminder for me for when I come to repeat this task next year and wish that this year’s me had written it down. This time, I made notes!

A mail merge involves importing a table of data (a spreadsheet in Excel form (“.xls(x)”), or as a tab- or comma-separated version in notepad) into a blank Word template so that the contents of each row in the spreadsheet is presented in label form.

You will need

  • Word – open a new document, this is where you’ll make the labels
  • Data file – Excel, notepad / text document, anything that has tabulated (columnar) data. Note that with Excel files with multiple tabs you can select which tab is used as the data source. Big fan of the text-based version though.
  • Patience and determination

1a. Open Word: Mailings tab » Start Mail Merge » Labels (select option / OK)

1b. Click on the ‘Label products’ drop-down menu and select brand, then size, then OK. You can also create your own label page from scratch with the New label option.
(I used “Avery A4 and A5 sizes”, L7163 (which has 14 labels to a page, 99mm in length, 38mm in height.)

1c. Word will now automatically populate a single page with largely invisible labels (‘Select All’ / Ctrl+A to see them) all but the first containing the phrase “Next Record”.

2. Select Recipients » Use an Existing List… (navigate to your data file, usually Excel (see section on troubleshooting) or notepad / plain text). If invited to “Open Document in Workbook:” click on menu to select which workbook (tab) of the spreadsheet you want to use. You can also select a cell range within that.

3. The ‘Edit Labels’ pop-up invites you to Insert Merge Field – click on that and add the fields (column headings) you want included, eg First Name will look like «First_Name». You can decide on the order and layout a bit at this stage but you’ve more control in the next stage so add them in and press OK.

4. Using the Home tab adjust the layout and appearance of the first record (top left, the only one that doesn’t say «Next Record» with colour, font, size, positioning etc. Once happy click back into the Mailings tab, click Update Labels to copy your layout across all labels. Then click on “Finish & Merge” and choose Edit Individual Documents… A new Word document will open with the finished labels which you can check and amend individually if necessary.

5. If you need to make changes affecting all labels just close the finished labels without saving and amend the underlying label design before repeating the Finish & Merge step.

6. While you can re-use this label ‘template’ with a different file (restart process from (2)) it doesn’t always work well and to be honest I’d start fresh, but I only do this once or twice a year. You may work out a better system for your needs.

Slight cheating
At point 1c you can stop if you only wanted to make a few labels manually, as you now have the basic template and can add in text and adjust layout, overwriting each «Next Record». For speed format the first label and copy to the rest by clicking the Update Labels button in the Mailings tab.

Troubleshooting
A friend sent me an Excel spreadsheet to make some labels and it misbehaved, giving me error messages when I tried to run the mail merge (it wanted me to download lots of unnecessary fonts). I completely solved that by selecting all the cells of the table and pasting into a blank notepad – it will keep the underlying formatting that lets it know where info in one column ends and the next column begins. It’s also a smaller file. Wikipedia has a good article on tab-separated data files.

On my Mac the Excel file wanted me to give it access to my keychain and I had to rebuff it a few times, also telling it not to download missing fonts. After clicking ‘deny’ and ‘no’ a few times it worked OK but it was quicker to paste the data into notepad and use that instead.

There’s an interim pop-up window if using a tab-separated notepad file where it asks what formatting it should use, for me it was a default Mac thing that worked (basically it asks ‘how should I read this file when transferring the contents to Word?’, with Excel it already knows as both are Microsoft products).

Font sizes
I had four lines of text using the following font sizes with Calibri or Arial.
FIRST_NAME – 36
SURNAME – 22 or 26
COMPANY – 14
DAY – 12     PHOTO OK – 12 (I’ve also written 14 in brackets in my notes).

Printing
This will depend on your printer but for mine it’s safer to print each page individually by selecting ‘print current page’ (the printers at work overenthusiastically default to double-sided!) and then feeding in the label sheet through the side tray, labels facing down but maintaining normal top to bottom (ie flipped only front and back).

Colouring in blocks or individual labels
Select labels to be coloured, right-click, borders and sharing, select colour and choose ‘apply to: Cell’ from the options then OK.

How to set up a rolling / looping PowerPoint for a display eg kiosk

At the final film event at the Charlton and Woolwich Free Film Festival (I’m one of the volunteers) last night we wanted to show a rolling display of photographs taken from our previous events. This is something that is reasonably straightforward using PowerPoint.

1. First create your presentation.

2. Click on the Transitions tab and make sure there’s a tick in the box on the left of ‘After:’. The box in the right lets you adjust the time (in seconds) to determine for how long the slide appears on the screen. In the example given it changes every 15 seconds. If you want all slides to be on screen for the same time click the Apply to All button and check a few slides to make sure it’s worked. Or adjust each slide individually.

Screenshot 2019-09-15 15.59.26

If you want the slide to appear gradually you can fiddle with the ‘Duration:’ options (3 sec shown) but you also need to click on one of the effect options (eg ‘morph’, ‘cut’ or ‘fade’) to activated it. If it’s on the default ‘None’ then nothing will happen.

3. Click on the Slide Show tab then the Set Up Slide Show option and choose ‘Browse at a kiosk‘. This will cause the presentation to show at full-screen size and loop continuously until you press Esc.

Screenshot of 'Set up Show' on Powerpoint, allowing you to make it loop continuously by setting it as a kiosk presentation

OR: If you want a bit more control you can choose the top option (‘Presented by a speaker [full screen]’) and also click ‘Loop continuously until Esc’ and making sure the ‘Using timings if present’ is ticked. This will let your presentation loop as before but also gives you the option to move a slide on (usually by clicking the space bar) if you want to.

Screenshot showing the manual version of how to make a presentation loop - it allows you to advance the slides as well

4. Run the presentation and check that it behaves as expected.

Here’s an example one to play with (looped) so you can see how changes to the the slide duration and effect duration options work in practice. It uses sound so silence your speakers or lower the volume if you are somewhere you’d rather not have that.

To run it as a loop just open the file and start the slideshow (see how below) – it will run until you press Esc. It has 3 slides in its deck.

Demo powerpoint presentation that loops

How to start a slide show

Use any of the four options shown (they appear randomly, not in a particular order)

  • Blue / grey = View, Slide show
  • Grey / orange = Mac menu for PowerPoint, Slide Show, play from start
  • Grey = Slide show icon at bottom right of window
  • Orange / grey = PowerPoint’s own menu, Slide Show, Play from start

Or use the shortcut key shown for Macs, or this info for PCs

This slideshow requires JavaScript.

 

How to save a Word doc as a PDF

It’s File / Save As…

Screenshot 2018-02-25 16.17.31

Then a window like this will appear…
From File Format options, choose PDF.

Screenshot 2018-02-25 16.13.18

In the screenshot above, from a Mac, the file location is accessed through ‘Favorites’ (seen on the left). The PDF will be stored in the same location as your source file, unless you specify otherwise.

On PCs the PDF will often immediately open once created unless you switch that setting off.

You can save Excel files and PowerPoint presentations as PDFs too (probably anything in the MS Office suite). For other file types the option give might be ‘Export as PDF’. See Zamzar below for converting other things to PDFs.

Further file conversion fun
PDFEscape is a free online tool that lets you do lots of things with PDFs. It’s aimed at Windows users but it seems to work fine on a Mac too. I’ve not used it to edit the text inside a PDF myself but have used it to combine two PDFs into one file: Converting a single A4 PDF into two A5 copies on one page

Zamzar will let you convert files into all sorts of formats (it’s amazing, never failed me yet). It works for documents, images, music, videos – even books, compressed files and CAD files (list of conversion types). You upload your file, it does the conversion then emails you when it’s done and you can download the converted file (which is later deleted from their servers).

 

 

 

 

 

When someone dies: capturing their voicemail messages

tl;dr version: play the message on one device while recording it with the voice memo on a smart phone or Vocaroo on a laptop, email yourself a copy / save the file on computer.

Photo credit: https://pixabay.com/en/microphone-stage-sound-1222302/

Well this’ll be a cheerful post 😉 But it was inspired by this lovely tweet from James O’Brien.

My dad died in Nov 2016 and at the time I was too all over the place to actually manage to record the last voicemail message he sent me a few days before (I’d spent the day with him the day before he died and spoke to him on the morning of the day he died so the voicemail message was from a few days earlier). I was very glad that I’d had the presence of mind earlier in the year to make a recording of a message he’d left me – he’d been ill and I think it had been in my mind that I might not have that many opportunities to record him. I’m glad I did. Sadly I didn’t think to do the same for my mum.

Here’s my dad, leaving a voicemail message on my phone, telling me about a BBC Four programme I’d have enjoyed about the London to Penzance overnight sleeper train which I’d travelled on the year before.

Although this particular post is about preserving sound here’s one about capturing text messages.

Before people die…
My advice is to ask your loved ones to record something, or capture their voicemail messages as you go along, as this person has done. Whatever you do don’t leave the voicemail message on the phone in case of accidental deletion.

If your loved one has a Wikipedia page they may even want to record something to append to their wiki entry! Find out more at the Wikipedia Voice Intro Project.

After people die…
It really seems to me that as soon as someone dies and you go through the process of registering the death etc etc that someone official should suggest capturing any old voicemail messages (texts too I suppose) as their capture is very time-limited. It would be great if phone companies and phone manufacturers made it super easy for people to access a better-quality recording. Meanwhile, here’s my rather old school way of doing it.

1. Making a recording of someone’s outgoing voicemail message
When you ring someone and they’re not there this is the message you hear from them before you leave your message. To record this some kind person (Pete Keen) has created a free online tool which will let you download the message as an .mp3 – see VMSave for more.

2. Making a recording of a message that someone’s left on your phone
I literally played the voicemail message through the speaker on my landline phone and held my iPhone microphone up to it, recording the message you can hear above with the already-installed Voice Memo app. It took a couple of goes to get a good recording, ensuring the right positioning of the microphone next to the speaker.

If the deceased person has left a message ON your iPhone (ie you can’t record it from the same decvice) then I’d suggest some borrowing someone else’s phone that has a voice memo recording facility, playing it on speaker phone rather than topping/tailing the microphone and speaker. If you have a laptop or computer with a microphone then you can use that to make a free recording with Vocaroo.

The recording results in an .m4a file which you can email to yourself from the phone (you can also use iTunes to move it around too) and you can listen to it on iTunes or the free VLC player and I’m sure plenty of other things too.

For sharing it with others possibly the best thing, beyond emailing a copy, is to download (or do it online) free Dropbox and add the file there. You can then share the link with anyone, only those with the link can access it but it is technically public. I have a sound-related blog and I pay an annual fee which lets me add any file (curiously WordPress dot com blogs don’t let you upload sound files without paying!) so that works for me. Much more public ways to share a sound file might include Soundcloud and things like that.

See also this postCapturing / sharing voice memos from iPhone and WhatsApp – it contains instructions on how to capture a voice message originally sent by WhatsApp and also has screenshots of the process involved in using the iPhone voice memo and sending the resulting file by email.

I’m hoping to find out other, better ways of making recordings and update this post – if you know of a simple method (that people who don’t have professional recording equipment could do) please let me know.

Further reading

 

• How to download Audioboom posts as mp3s

Audioboom is no longer supporting free accounts, they will not delete your content for three years though so don’t panic (yet). However at the end of October 2017 they’ll make all free accounts private, so if you have your sound-posts embedded in other places then they’ll no longer work. They will help you migrate your RSS feeds (more info here) though.

This embedded post of mine will presumably stop working properly in a month or so…

Of course Audioboom are entitled to start charging and restrict services from non-payers, it’s just a bit frustrating for individuals (who own the content) and the wider internet which suffers when embedded audio files disappear on websites along with comments. Basically this ‘breaks the internet’ a bit.

Here’s one way of downloading any Audioboom sound file, as an mp3. I’m investigating better solutions for people with lots of files. Below that are suggestions on how to capture and share them online again.

Table of Contents

  • 1a. How to download any individual Audioboom file as an mp3
  • 1b. Bulk downloading
  • 1c. Additional information on downloading (accompanying images)
  • 2. Where to put your files now you’ve downloaded them
  • 3. Background to this story

 

1a. How to download any individual Audioboom file as an mp3

  1. Visit the page of the sound file, eg here’s one of mine
    Screenshot 2017-09-02 14.29.46
  2. Add .mp3 to the end of the URL in the address bar, press enter – this automatically changes the page to an mp3 player page. (Commenter Leyton, 3rd comment below, found that for Chrome this stage downloaded the mp3 automatically).

    Screenshot 2017-09-02 14.30.46
    Screenshot 2017-09-02 14.32.09

  3. Hover over the play icon (it goes blue) and right-click Save Audio As…
    Screenshot 2017-09-02 14.33.06
  4. Repeat for other files
  5. If you have a lot of files (I have 85 clips to download) there’s probably a more efficient way (I don’t know it yet but will gladly link if I hear of it). I have emailed Audioboom support to ask them support@audioboom.com

There are some techy suggestions on Twitter which include uncovering them from iTunes by subscribing to the podcast RSS. To be honest I’m looking for a ‘Download archive’ button as on Twitter 🙂

1b. Bulk downloading
Phil Cooper has kindly commented (at the end of this post) but I’m putting his text here for extra usefulness.

“For bulk downloading of Audioboom MP3 files, if you have a list of all of the URLs, you can use a free command-line utility called wget. It was originally written for GNU Linux, but a Windows version also exists. Using a text editor such as Geany for Linux or Notepad++ for WIndows, write a BASH script or a Windows batch file using the list of URLs, create a directory (folder) where you want to save the files, open a command window in that directory and run the script.”

1c. Additional information on downloading

  1. You can also download the image that accompanied your ‘boom’ (or ‘boo’ as they used to be called when the service was Audioboo) with right-click Save as too OR hover over the pic and take a screenshot, that way you’ll get an image of the little soundwave, that also gives information.
  2. For completists you might want to number your sound files and have an accompanying readme.txt type of file that includes info about the date originally published and the hashtags.

2. Where to put your files now you’ve downloaded them
You can upload sound files as a video (static image) to YouTube. WordPress also lets you pay £80 a year for the ability to upload more files than the basic ones (without it you can’t upload sound files, only embed them from somewhere else) – that way you can have an on-page audio player and people can listen directly. Or you could put them in Dropbox and share a public link to them for people to download.

3. Background to this story
I discovered this via Paul Bradshaw and Documentally.

• Excel ‘concatenate’: how to combine FirstName LastName columns into one column – Name

Formula
The formula is of the format =CONCATENATE(A1,A2) which will combine the separate names in Cell A1 and Cell B1 into one.

concatenate

On my version of Excel this format will include a space between the two but you can force one if yours doesn’t, with =CONCATENATE(A1, ” “, B1).

Note that Cell C1 is highlighted (surrounded by a green border). If you have lots of names in columns A and B you can double click on the tiny green square at the bottom right and the formula will cascade all the way down your list, stopping at the last item.

This format will also work =A1 & A2 (or if you need a space it’s =A1 & ” ” & A2)

Beware: do not now delete columns A and B or your newly created column C will disappear as each cell is actually a formula, contingent on the contents of other cells. If you want a text-only version I think the quickest way to do this is to select Column C and copy (Ctrl+C) its contents, then paste temporarily into a text file (eg notepad.exe) which will paste the words you see in the column and not the underlying maths. Then paste from the notepad file back into an empty column and you can delete the other three safely.

Why not just collect people’s names as names rather than FirstName LastName?
Sometimes it’s handy to be able to order a spreadsheet of people’s names by their surname as well as by their first name, so it’s quite handy to have one column for their forename and another for their surname.

Further reading
Microsoft’s help page on the CONCATENATE function

This post is a neatened update of a post originally published on my main blog (I’m gradually transferring the techy posts I’ve published there… here).

 

 

How to remove the audio track from a video made on an iPhone, using iMovie

Sometimes I take a video of something and on listening to it hear other noise or conversation that I don’t particularly want to include. Here’s how I get rid of it in ‘post-production’.

This may not be the best way of doing it and I’m certain it’s not the only way. However, it works and doesn’t involve downloading any extra apps or spending money, so it’ll be the method I’m likely to favour. If you know of a better way, others might be interested so please feel free to share your improvements and suggestions in the comments below.

This post assumes that you have an iPhone with the built-in camera, and iMovie apps. I’m running iOS 7.6 (if you’ve got a higher iOS then my screenshots might look a little different from yours, but hopefully not so much as to make the instructions unworkable).

Here are the basic instructions, repeated below with the addition of screenshots

1. Record your video
2. Open iMovie
3. Click the + at bottom right to create new project
4. Click Movie
5. Click Create Movie
6. Tap the icon to ‘insert media’
7. Select your chosen video, it goes yellow
8. Click the down arrow to insert
9. Your video appears as a panel of images, and the cursor leaps to the end
10. Click the video panel, it goes yellow
11. Audio is already selected, click the two dots below the dustbin, click detach
12. The audio track is now separated from the video and you can, if you wish, move it into a different position. It’s already selected (yellow) so press delete to remove it
13. Try out your now silent video, all the sound should have gone
14. To export it click on the back arrow at the top, then the upload icon for sharing options. Your options will depend on your phone and apps but I’ve got Facebook, YouTube, Vimeo etc. and if I scroll along there’s the email option at the end and WhatsApp. The save video, in the lower down (not brightly coloured) panel will save it to your camera roll, from which you can also email and send it other places etc.).

To share it on Twitter, note that it will need to be reduced in length to just a few seconds and you’ll need to save this ‘iMovie project’ to your camera roll to do so (and ensure that you’ve granted Twitter access to your iPhone’s camera roll [at some point I might add something about how to do this but it’s in Settings somewhere]), you can also email it to yourself.

You’ll end up with a much smaller, squarer version of your video, rather than the rectangular one you created. I’ve no idea why, nor how to make it larger. If you know a better way that will let you end up with a video that’s identical to the one you recorded minus the audio, please let me know. I’m less interested in costly professional tools though.

The same again but with added images

1. Record your video
2. Open iMovie (it was already installed on my phone)
3. Click the + at bottom right to create new project

photo 1(5)
4. Click Movie (in blue, below)

photo 2(4)
5. Click Create Movie (top right in pale blue, below)

photo 3(3)
6. Tap the sprocketed film + musical note icon at the top to insert media

photo 4
7. Select your chosen video, it goes yellow – you can slide the yellow boundaries to make your clip shorter too.
8. Click the down arrow to insert

photo 1(5)
9. Your video appears as a panel of images, and the cursor leaps to the end

photo 2(4)
10. Click the video panel, it goes yellow

photo 3(3)
11. Audio is already selected, click the two dots below the dustbin, click Detach

photo 4
12. The audio track is now separated from the video and you can, if you wish, move it into a different position. It’s already selected (yellow) so press the dustbin icon to delete it

photo 1(5)

13. Try out your now silent video, all the sound should have gone
14. To export it click on the back arrow at the top, then the upload icon for sharing options. Your options will depend on your phone and apps but I’ve got Facebook, YouTube, Vimeo etc. and if I scroll along there’s the email option at the end and WhatsApp. The save video, in the lower down (not brightly coloured) panel will save it to your camera roll, from which you can also email and send it other places etc.).

photo 2(4)

Above: Click the left-facing arrow in the top left to start the export process.

Below: you’ll see this screen, choose the upload icon in the middle to upload your silent film.

photo 3(3)

Below: Mail is at the end of the list of things you can send the file to and you can also save it to your camera roll.

photo 4