Problem: I’ve got a list of under 50 subscribers to a MailChimp email list, but, presumably since I haven’t used it in years, MailChimp (with no warning or notice) deactivated the account. I discovered this because I had decided that the circumstances were finally right to start using the list, but I can’t login to MailChimp due to the deactivation. They say I must create a new account. Well, yeah … and then I’ve got to ask 50 people to resubscribe. How can I accomplish this as easily and painlessly as possible for everyone?

The solution is not to simply add them all to a new MailChimp account list and send them an email … if even a couple of the addresses don’t work, I could get my account shutdown, or at least a major warning. Having endured that nightmare once upon a time, I’d rather not go through that again. Indeed, their own suggestion in this scenario is to send out an email from a personal account to verify that the addresses are still good (and that someone still wants to be on my list) … THEN either add them to a list or (better yet) ask them to re-subscribe.

The good news is that I already have a FileMaker database of subscribers. Whenever MailChimp would send me a “you’ve got a new subscriber” email, I’d copy and paste it into a FileMaker field and a script I wrote would parse out the information. (Zapier can do this, too.) Plus, I still have all those email notifications from MailChimp, so I was able to double-check that I had everyone into the database. The one annoyance I’ve had over the years is that MailChimp occasionally changes its notification email design, messing up my parsing. I’ve had to do some editing a few times, but I always get it working again.

… I want to offer a personalized link …

I know it’s not much, but something as minor as having to retype their name and email address could be a barrier to someone re-upping their subscription. I want to offer a personalized link in an email which would not only take them to the form on the web, but would automatically fill in their name and email address—after all, I already had that information. All they would have to do is press the “Subscribe” button—that’s what I call friendly. And easy! What wasn’t easy or straightforward was figuring out how to do it, but, through trial-and-error, I found the answer. (And I would have found the solution faster if I had remembered the term query string in my searches.)

MailChimp gives a shortened URL for the form link. If you visit that link, you’ll see the “real” (i.e., full) URL in the URL bar at the top of the browser. Using that full link, one must then add query strings at the end so that the form’s fields can be populated automatically as the form loads in the browser. The key part here is getting the field names right (MailChimp calls them field tags). When building a form in MailChimp, select a field on your form, then check the Field tag box under the Field Settings tab for the name to use in the query string.

This is what the link looks like:

https://[mailchimp account name].us19.list-manage.com/subscribe?u=[long string of characters identifying the form]&EMAIL=name@domain.com&FNAME=John&LNAME=Jones

Obviously, the bits between the [ ]s would be replaced with the relevant account and form identifiers, and the bit before “subscribe” will probably look a little different, too, depending on which MailChimp server your account is hosted on, but you get the idea.

Now that we know how to form the link, what’s the best way to go about automatically generating a personalized version of them in an email to each subscriber on the list?

This is where the magic of FileMaker comes in. FileMaker is perfectly suited to this task because it can not only manipulate the data into the necessary URL, it can generate the email, too. I made a script that grabs each subscriber’s information and combines it with the part of the link that doesn’t change. Then, using the Send Mail script step, an email is created for each person with the personalized link. It’s a pretty straightforward script, actually:

Show All Records

Go to Record/Request/Page [ First ]

Loop

Set Variable [ $subscribe_link; Value:GetAsURLEncoded ( "https://[mailchimp account name].us19.list-manage.com/subscribe?u=[long string of characters identifying the form]&EMAIL=" & main::email & "&FNAME=" & main::first & "&LNAME=" & main::last ) ]

Set Variable [ $email_body; Value:"Dear " & main::first & "," & ¶ & ¶ & "You kindly signed up for our email announcements list back on " & main::date & ". We use MailChimp to send those emails. We had to create a new account, and that requires us to ask that you confirm your subscription to our list." & ¶ & ¶ & "If you don't wish to remain on the list, then just do nothing and you won't be re-added. If you do wish to remain on the list, however, the process is simple and will take only a few seconds. Click the link below, fix any incorrect information in the boxes, and then press the Subscribe button. We hope you'll rejoin, and apologize for the inconvenience." & ¶ & ¶ & $subscribe_link & ¶ & ¶ & "Best wishes," & ¶ & ¶ & "name" ]

Send Mail [ Send via E-mail Client; To: main::email; Subject: "Re-subscribing to our email list"; Message: $email_body ]

Go to Record/Request/Page [ Next ; Exit after last: On ]

End Loop

But there’s always (at least) one snag. The URL is way too long. Some email clients make messes of long URLs. Often they don’t work right when clicked because only part of the link gets sent to the browser. To fix this, a URL shortening service will be needed. I’ve run into further snags trying to get this to work. I haven’t given up yet, but at this writing I haven’t had the time to devote to finding the best solution. When I get this (c)licked, I’ll let you know. ;)) ◼︎