SMS from mobile numbers in spreadsheet

[steve.waye]

Hi all. Hoping someone can help with some VBA.

I have a list of students (up to 500) in a spreadsheet. The list includes email addresses and mobile numbers.

To send an email to selected students I just filter the spreadsheet and highlight the email addresses that I want to use, and then paste these into my email client. Not too cumbersome.

Sending an SMS however is a little more involved...

• First I select the cells with the relevant mobile numbers (which are formatted as 0412 345 678).
• Then I paste these into another sheet which automatically converts the numbers into the correct SMS format for my email client (formatted as 0412345678@sms.domainname.edu.au).
• Then I copy the formatted numbers and paste them into the "To:" field of my email client.

This is easier than typing each mobile number manually, but it would be better if I could simply click a button at the top of the page that did the whole process automatically...

• Highlight relevant mobile numbers
• Click an "SMS Selected Students" button
• Email client automatically launches a new email page with formatted numbers entered

My VBA experience is limited to finding solutions on the internet and modifying to suit, so I often don't have a full understanding of what is happening with the VBA .

Any help would be appreciated.

Thanks, Steve

 

[sykes]

Strewth Mate!

Morning Steve (or afternoon, where you probably are).

I wondered if you could manage an extra column in your data - so that you could just fill-down the formula (col C in my example), then once the mobile phone number's entered in col B (in my example), the corresponding SMS cell (in C) is formatted correctly.
Presumably, you could then filter, as you're already doing, and use the same method for sending.

test.xlsm
	A	B	C
1	Name	Mobile	Formatted mobile
2	Joe Bloggs	0412 345 678	0412 345 678@sms.domainname.edu.au
3	Kylie M	0414 234 567	0414 234 567@sms.domainname.edu.au
4	Danny M	0421 123 789	0421 123 789@sms.domainname.edu.au
5	Dame Edna	0412 321 654	0412 321 654@sms.domainname.edu.au
6		0142 568974	0142 568974@sms.domainname.edu.au
7			@sms.domainname.edu.au
8			@sms.domainname.edu.au
GRASS

Cell Formulas
Range	Formula
C2:C8	C2	=TEXTJOIN("",TRUE,B2,"@sms.domainname.edu.au")

 

[steve.waye]

Strewth Mate!

Morning Steve (or afternoon, where you probably are).

I wondered if you could manage an extra column in your data - so that you could just fill-down the formula (col C in my example), then once the mobile phone number's entered in col B (in my example), the corresponding SMS cell (in C) is formatted correctly.
Presumably, you could then filter, as you're already doing, and use the same method for sending.

test.xlsm
	A	B	C
1	Name	Mobile	Formatted mobile
2	Joe Bloggs	0412 345 678	0412 345 678@sms.domainname.edu.au
3	Kylie M	0414 234 567	0414 234 567@sms.domainname.edu.au
4	Danny M	0421 123 789	0421 123 789@sms.domainname.edu.au
5	Dame Edna	0412 321 654	0412 321 654@sms.domainname.edu.au
6		0142 568974	0142 568974@sms.domainname.edu.au
7			@sms.domainname.edu.au
8			@sms.domainname.edu.au
GRASS

Cell Formulas
Range	Formula
C2:C8	C2	=TEXTJOIN("",TRUE,B2,"@sms.domainname.edu.au")

Yes, that's pretty much what I'm already doing, but using CONCATENATE to join the numbers to the @sms string, and doing it on a separate page so as to keep the main sheet clean.

I was hoping there might be a simpler way that's hidden in VBA.

Something like this...

selectCellsManually
clickSMSbutton
removeBlanksFromMobileNumbers
concatenateNumbersWith@sms.domain.edu.au
openEmailClient
pasteAllNumbersIntoTheToFieldOfEmailClient

BTW, I do know that the above isn't code, just my gibberish.

Also, thanks for the Reply which I only just saw since the email went into my Junk Mail. I've now added MrExcel as a safe sender (which I thought I had done 10 years ago).

 

[sykes]

Well...
Not sure about code for opening/manipulating your default email client (which may or may not be Outlook) but there are tons of posts on this forum about doing that bit of your requirement. You'd just need to bolt the code onto the end of this...

This will satisfy the first part of your request.
Put an activeX CommandButton on your page (mine's cleverly called "CommandButton1").
Paste this into the button's _click event:

Dim cl As Range
Dim str As String

    For Each cl In Selection
        str = str & Replace(cl.Value, " ", "") & "@sms.domainname.edu.au" & ";"
    Next
str = Left(str, Len(str) - 1)
'Debug.Print str
MsgBox str

... so once you've finished, the whole thing should look thus (except that the name of your CB may be different):

Private Sub CommandButton1_Click()
Dim cl As Range
Dim str As String

    For Each cl In Selection
        str = str & Replace(cl.Value, " ", "") & "@sms.domainname.edu.au" & ";"
    Next
str = Left(str, Len(str) - 1)
'Debug.Print str
MsgBox str
End Sub

Now, if you select several of the mobile number cells, and press the CB, it should give you a messagebox with the whole string, already formatted for your email client.
This is just to ensure that the numbers are formatted correctly for you.
You can then use "str" in the code, when you want to inject the SMS string into the email client "To" field.

HTH

 

[sykes]

PS - I've never heard of an email client being used to send SMS, so this is, presumably some kind of purpose-built system (school, or uni?).
I assume that the "SMS" in the domain name, is a server, which is capable of taking the username part of an email a/d, and re-sending the body of the email, as an SMS, instead.