I recently had a request to add a feature to an MS Access application that would send text messages to mobile phones when a new record was added to a certain SQL Server table.
I was reminded that mobile phone carriers have the ability for text messages to be sent to their customers mobile phones by sending email messages to a combination of a customer’s mobile phone # and a specific carrier email domain. For example, Verizon’s is “vtext.com” so to send a text message to 555-888-1212 by sending an email, you would use firstname.lastname@example.org.
To manually test, you can simply enter the email address in your email client, add a subject and something in the body and click Send and it will show up on whatever phone # you used in front of the “vtext.com” (assuming the phone # was with Verizon).
For my specific implementation, since our Access front-end was using SQL Server as the back-end database, we simply added a trigger to the SQL Server table that sent an email whenever a new record was added. In our case, we additionally had a DepartmentID on the record that we used to look up the mobile phone # in a related Department table and sent a message to that number. Using the sp_send_dbmail stored procedure built-in to SQL Server, the below syntax was in our trigger with a Select statement above it where we looked up the phone # from a Department table using the ‘Inserted’ keyword to get the ID from the just-inserted record.
EXEC MSDB.dbo.sp_send_dbmail @profile_name=’sql_mail_profile’, @recipients = ‘email@example.com’, @Subject = ‘New Record Entered Alert’, @body = ‘Log in to the system and see the details’;