![how to write an automatic email sender how to write an automatic email sender](https://i.stack.imgur.com/a5iKx.jpg)
Then we use the “Set” method to create the Outlook objects, which contain the properties of the email (address to, cc, subject, body, attachments, display, send). First, we declare our email objects, OutApp and OutMail, using the “Dim” statement. Once our variables are created, we can now implement our email function. Sub test_email_template() Dim name, email, body, subject, copy, place, business As String name = Split(Range("A2").Value, " ")(0) 'Grab first name only email = Range("B2").Value body = ActiveSheet.TextBoxes("TextBox 1").Text subject = Range("C2").Value copy = Range("D2").Value business = Range("E2").Value place = Range("F2").Value ' replace place holders body = Replace(body, "C1", name) body = Replace(body, "C5", business) body = Replace(body, "C6", place) End Sub
#How to write an automatic email sender free
Feel free to use the “Replace” method for all your place holders. To change the value of the “C1” place holder to the name, you can use the “Replace” method to replace any instance of “C1” in the body text with the name variable: body = Replace(body,"C1",name). Sub test_email_template() Dim name, email, body, subject, copy, place, business As String name = Split(Range("A2").Value, " ")(0) 'Grab first name only email = Range("B2").Value body = ActiveSheet.TextBoxes("TextBox 1").Text subject = Range("C2").Value copy = Range("D2").Value business = Range("E2").Value place = Range("F2").Value End SubĪs mentioned previously in the setup, we added place holders in the body of the email, such as “C1” for the name of the recipient. For example, to assign the email address in Cell B2 to our email variable, we can use the Range property: email = Range("B2").value. Next, we can assign values to our variables by referencing the template we created in the setup. The variables include name, email, body, subject, copy (cc email), place, and business. In your text editor (Alt+F11), we start by declaring the variables we will use to send our emails (“Dim” statement).
#How to write an automatic email sender code
We will then modify this code to create emails for the entire list of contacts by adding a “Do While” loop. Now it’s time for the code! We will begin by creating the function to generate an email for the first contact only (Row 2). Similarly, “C5” and “C6” from the text box are replaced with the values in columns E and F respectively. In the code below, you will see how we replace “C1” with the recipients’ names from column A. I used “C1” to represent column A’s values (you can use any place holder). For example, the opening statement says, “Hi C1,”. Within the body, the user can put place holders to add customized text. To add text to the body of the generated email, the user can interact with the text box (Insert Tab -> Text -> Text Box). I also added two buttons to execute our VBA macros (Developer Tab -> Insert -> Button (Form Control)). In this example, I have the following columns for contact information: Name (A), Email (B), Subject (C), CC (D), Business (E), and Place Met (F). Setupīefore you write your VBA program, you must set up your Excel sheet to let you run through a list contacts. Don’t worry if you don’t understand the meaning of these properties yet the code will be discussed soon in more detail. If you want to send the emails automatically without showing the composed email (like the demo above), you can uncomment the “send” property and comment out the “display” property in the VBA code.
![how to write an automatic email sender how to write an automatic email sender](https://www.everydaynodaysoff.com/wp-content/uploads/2010/08/Business-Proposal-From-A-Soldier-Scam.jpg)
Notice that the emails don’t automatically send, and instead display the email contents for you to check before clicking send. In the demonstration above, you will see that two emails are created when you click the “Send Mass Email” button because there are only two contacts in the list. The “Send Mass Email” button will generate personalized emails to everyone in your list of contacts. Once you feel confident with the message format, click the second button, “Send Mass Email.” You can test if the email is in your desired format by clicking the first button, “Test Email Template,” which will display the email for the first contact only.
![how to write an automatic email sender how to write an automatic email sender](https://wmstatic.global.ssl.fastly.net/ml/4260620-f-3e987d15-73df-49a0-8a43-2aecb3dd3f17.png)
The body of your email is formulated inside the input box (on the right).
![how to write an automatic email sender how to write an automatic email sender](https://images.macrumors.com/article-new/2018/02/out-of-office-rule-text-800x521.jpg)
The program works by iterating through a list of contacts (columns A to F in this example) and generating an email for each contact. It’s best to get a high-level overview of the program before jumping into the code.