Excel VBA Form for Message box and Email

Jdogg2022

New Member
Joined
Mar 21, 2022
Messages
22
Office Version
  1. 365
Platform
  1. Windows
Hi All,

Hoping someone can help me on this,

I am trying to have a message box populate in my code so that it could execute in the body of an email of more than one line. I can get it working for a single line, but I want to be able to do multiple lines and figure the best way to do this would be to do a user form with a text box.

I am at the point of where I can get the macro to show the form, but I am struggling with getting the user input to be executed into the body of the email

My variable {W_Bodymessage}

Below is my code

Option Explicit

Sub Lettersend()

Dim OutApp As Object
Dim OutMail As Object
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
Dim fd As Office.FileDialog
Dim strFile As String
Dim IsCreated As Boolean
Dim i As Long
Dim PdfFile As String ', Title As String
Dim PdfFileTC As String
Dim OutlApp As Object
Dim i2 As Integer
Dim W_Customer As String
Dim W_Email_Address As String
Dim W_Customer_Name As String
Dim W_Excel_Tab As String
Dim W_Month_Rate As String
Dim W_Message As String
Dim W_BodyMessage As String



'Adding Message Dialogue box to Create Subject Header
W_Message = "Enter The Subject header you wish to have to Email Recipient"
W_Message = InputBox(W_Message, "Subject Header")
If W_Message = "" Then
Exit Sub
End If
'Adding Message Dialogue box to Create Text for body of Email
W_BodyMessage = "Enter The Text you wish to put in the body of the email"
'W_BodyMessage = InputBox(W_BodyMessage, "Text you wish to put in the body of the email")

Messagebox.Show



If W_BodyMessage = "" Then
Exit Sub
End If

'Setting up the File Select (for PDF)
Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
.Filters.Clear
.Filters.Add "PDF Files", "*.PDF?", 1
.Title = "Choose an PDF file"
.AllowMultiSelect = False

.InitialFileName = "G:\Transport\FCL Delivery Contacts\Transport Email Notifications\"
If .Show = -1 Then
strFile = .SelectedItems(1)
Else
Exit Sub
End If
End With

'Start Loop
i2 = 2
For i2 = 2 To 1000
Range("AH" & i2).Select
If Range("AH" & i2).Value = "" Then
Exit For
Else
'W_Customer = Range("J" & i2).Value
W_Email_Address = Range("AH" & i2).Value
'W_Customer_Name = Range("B" & i2).Value
'W_Excel_Tab = Range("D" & i2).Value
'W_Month_Rate = Range("E" & i2).Value


' Use already open Outlook if possible
On Error Resume Next
Set OutlApp = GetObject(, "Outlook.Application")
If Err Then
Set OutlApp = CreateObject("Outlook.Application")
IsCreated = True
End If
OutlApp.Visible = True
On Error GoTo 0
On Error Resume Next
' Change the mail address and subject in the macro before you run it.
With OutlApp.CreateItem(0)
.To = W_Email_Address
.CC = ""
.BCC = ""
.Subject = W_Message
.Body = W_BodyMessage
.Attachments.Add strFile
' Try to send
On Error Resume Next

.Send
'<-- Remove "'" to send

Application.Visible = True
If Err Then
Msgbox "E-mail was not sent", vbExclamation
Else
'MsgBox "E-mail successfully Created", vbInformation
End If
On Error GoTo 0

End With

' Delete PDF file
'Kill PdfFile

' Quit Outlook if it was created by this code
If IsCreated Then
OutlApp.Quit
End If

' Release the memory of object variable
Set OutlApp = Nothing

End If

Next i2
'End With

End Sub
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Forum statistics

Threads
1,223,240
Messages
6,170,951
Members
452,368
Latest member
jayp2104

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top