Emails from a form to a list

[Cymru]

Hi - I have written (with some borrowed code) asome VBA code that allows a user to send an ad-hoc email with variable heading, body and attachment. However the body input only shows an input box for this which means the user can only see a limited section of their main message. Is there a way to increase the size of the input window to say 10 lines?
Here is my code -

Private Sub SendRem_Click()
On Error GoTo Err_Handler

Dim db As DAO.Database
Dim MailList As DAO.Recordset
Dim OutApp As Object
Dim OutMail As Object
Dim Subjectline As String
Dim MyBody As String
Dim MyBodyText As String
Dim strTo As String
Dim CourseNum As Single
Dim qdf As DAO.QueryDef
Dim strPath As String
Dim ClassAttach As String
Dim Test As String


' First, we need to know the subject.
Subjectline$ = InputBox$("Please enter the subject line for this mailing.")
' If there's no subject then exit.
If Subjectline$ = "" Then
MsgBox "No subject line, no message." & vbNewLine & vbNewLine & _
"Quitting...", vbCritical, "Link System"
Exit Sub
End If

' Now we need to put something in our letter...
MyBodyText$ = InputBox$("Please enter text for the body of the email.")
' if there is no message then exit
If MyBodyText$ = "" Then
MsgBox "No message entered" & vbNewLine & vbNewLine & _
"Quitting....", vbCritical, "Link System"
Exit Sub
End If

Set OutApp = CreateObject("Outlook.Application") ' Now, we open Outlook for our own device..
Set db = CurrentDb() ' Set up the database and query connections
CourseNum = Me.CourseID
Set qdf = db.QueryDefs("MyEmailAddresses")
qdf.Parameters("CourseID") = CourseNum
qdf.OpenRecordset
Set MailList = qdf.OpenRecordset()

' this is where we loop through our list of addresses,
' adding them to e-mails and sending them.

Do Until MailList.EOF
strTo = MailList("EmailName")
Set OutMail = OutApp.CreateItem(0) ' This creates the e-mail
With OutMail
.To = strTo
.Subject = Subjectline$ 'This gives it a subject
.Body = MyBodyText 'This gives it the body

'Add attachment to emails
strPath = DLookup("SFileLocation", "Settings", "SNumber = 1") ' this looks at the Settings Table for the default file location
'If you want to send an attachment create a pdf file called ClassInfo.pdf in the default folder specified above
ClassAttach = strPath & "ClassInfo.pdf"
Test = Dir(ClassAttach) 'tests if there is a file existing
If Not Test = "" Then
.Attachments.Add ClassAttach
End If

'This sends it!
.Send
' to see the e-mail
'instead of automaticially sending it.
'Uncomment the next line ".Display"
'And comment the ".Send" line above
'.Display

End With
'And on to the next one...
MailList.MoveNext

Loop

'Cleanup after ourselves
Set OutMail = Nothing

'Uncomment the next line if you want Outlook to shut down when its done.
'Otherwise, it will stay running.

'OutApp.Quit
Set OutApp = Nothing

MailList.Close
Set MailList = Nothing
db.Close
Set db = Nothing

Exit Sub

Exit_Handler:
Exit Sub
Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description, , "Link System"
GoTo Exit_Handler

End Sub

 

[Cronk]

You could use a form to input required information to generate email(s)

See attached for example from one of my databases.

 

[The_Doc_Man]

I have used a larger text box for which the properties included that I enabled the vertical scroll bars. If you have a long-winded writer, you will run out of room no matter what you do - but it is possible to allow more of the message to be seen at once AND scroll to see different parts.

 

[Cymru]

Tks Doc Man except I can't see how to use a larger text box as I haven't created a form as Cronk suggested. Is this the only or is it the best option?

 

[Cronk]

"Is this the only or is it the best option?"

Lots of options for you. Open notebook, Word, Excel, save the inputted text and import into your database. You could have the user enter the data directly into a memo field in an table, or even open and display a new Outlook message and type it directly there.

What is "best"? Might be a subjective opinion but I consider my method the best for my users.

 

[OTWarrior]

Tks Doc Man except I can't see how to use a larger text box as I haven't created a form as Cronk suggested. Is this the only or is it the best option?

You could use multiple inputboxes (one for each sentence) and combine them afterwards into one long string, though this would be time consuming.

If you built a form, you could possibly put an "onTimer" event (as the user would be typing in the box at the time, the value hasn't yet changed, so you would need to change focus, test the value, and return focus to the text box) to check the length of the string within the text box and change the height of the textbox object accordingly. Rather clunky, but it could work.

Another option would be for the user to write the text in another program, such as word, excel, outlook (even a simple txt file created in Notepad) and import the text when creating the email. This would be more of a two step approach, but would have the benefit of the user knowing the interface and being able to type as much as they like (provided the import handles the string size).

Hope that helps.