[RESOLVED] VBA macro to send bulk email

[ROB IN RWC]

I need help.

I have a spreadsheet that has multiple columns, 3 of which contain names, email addresses and a column for "confirmed". for simplicity call them A:C

I am trying to create a macro that will open a userform.
The form has two text boxes like an emial.

1. is labled subject
2. is labled body (side question...how do I allow for the "enter" key to be used in a text box to create more than 1 line?)

There are two cmd buttons
1. Cancel (obvioulsy if the user wants to exit without sending)
2. Send

Once the send button is clicked on......

It will take the values from the two text boxes and use them to send a bulk email to everyone on the list who has an email entered, but doesn't have an "X" in the confirmed column.

This is the code I have for the emial part

Sub Mail_small_Text_Outlook()
'You must add a reference to the Microsoft outlook Library
    Dim OutApp As Outlook.Application
    Dim OutMail As Outlook.MailItem
    Dim VarBody As String
    Dim VarSubj As String
    VarSubj = "Userform1.txtSubj.text"
    VarBody = "Userform1.txtBody.text"
    Do
    If ActiveCell <> "" And ActiveCell.Offset(0, 1) <> "X" Then
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(olMailItem)
    strbody = VarBody
 
    With OutMail
        .To = ActiveCell.Value
        .CC = ""
        .BCC = ""
        .Subject = VarSubj
        .Body = VarBody
        .Send   'or use .Display
    End With
 
    Set OutMail = Nothing
    Set OutApp = Nothing
    ActiveCell.Offset(0, 1) = "X"
    Else: ActiveCell.Offset(1, 0).Select
    End If
    Loop Until ActiveCell.Value = "send_email_end"
End Sub

I need big help on the code for the userform.

Thanks so much

[khader82]

Hi I am not really sure of where u have the trouble..looks like u want to open up the form frm excel sheet..

u can have a command button next to the 3 cols tht u have on the spreadsheet and code it to open up the userform userform1.show from there u can take it further..

if u have some trouble with sending the mails do let me know..

[DKenny]

2. is labled body (side question...how do I allow for the "enter" key to be used in a text box to create more than 1 line?)

Set both the MultiLine property and the EnterKeyBehavior property of the textbox to TRUE.


As for showing the form. Here's some sample code. In this sample I have assumed that the form is called UserForm1. You will need to change that to the name of your form.

Sub SampleShowForm()
Dim fMailForm As UserForm1
    
    'Create a new instance of Userform1
    Set fMailForm = New UserForm1
    
    'Show the form
    fMailForm.Show
    
    'Other code here
    
    
    'Remove the instance when processing is complete
    Set fMailForm = Nothing
End Sub

[zaza]

I hope this isn't for spamming.

[ROB IN RWC]

No it's not for spamming. It's for a committe that I am on. I need to send emails to confirm times that postions start. I want to be able to send a reminder email to everyone at once that I hadn't previous send one to.

I've got it to work except for a couple of issues thanks to Kenny's help.

Option Explicit
Sub SampleShowForm()
Dim fMailForm As frmEmail
    
    'Create a new instance of Userform1
    Set fMailForm = New frmEmail
    
    'Show the form
    fMailForm.Show
    
    'Other code here
    'You must add a reference to the Microsoft outlook Library
    Dim OutApp As Outlook.Application
    Dim OutMail As Outlook.MailItem
    Range("e2").Select
    Do
    If ActiveCell.Value <> "" And ActiveCell.Offset(0, 2).Value <> "X" Then
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(olMailItem)
    With OutMail
        .To = ActiveCell.Value
        .CC = ""
        .BCC = ""
        .Subject = frmEmail.varSubj
        .Body = frmEmail.varBody
        .Send   'or use .Display
    End With
 
    Set OutMail = Nothing
    Set OutApp = Nothing
    ActiveCell.Offset(0, 2) = "X"
    ActiveCell.Offset(1, 0).Select
    Else: ActiveCell.Offset(1, 0).Select
    End If
    Loop Until ActiveCell.Offset(0, -3).Value = ""
    
    'Remove the instance when processing is complete
    Set fMailForm = Nothing
End Sub

In this code, I am attempting to call the code from this form...

Option Explicit
Public varSubj As String
Public varBody As String
 
Private Sub cmdCancel_Click()
Unload Me
End Sub
Private Sub cmdEmail_Click()
varSubj = frmEmail.txtSubj.Text
varBody = frmEmail.txtBody.Text
Me.Hide
End Sub

Two issues...
1. There are two text boxes. One for the subject and one for the body of the email. I defined them by using a public variable but it didn't seem to work when I tried to call them. I want the emails that are generated to use the same subject and body. Also, there is a cmdSend button and a cmdCancel button.

2. The test for whether an email has been sent previosly is to check the cell two columns to the right for an "X". It works perfectly, except when I hit the cancel button. It runs the remainder of the routine anyway.

Help!!

[ROB IN RWC]

Just an FYI, I figured this one out!
I'm proud of me.