Solved: Sending an email from excel

[phendrena]

Hi there,

Is there a way that i can get excel to send the data on a user form via email? I've done a search of the forum and found various threads on this subject however they all reference Outlook as the mail client, i would be sending email using Lotus Notes running via domino server.

Can it still be done, and do i have much control over how the data would be formatted?

The data is sent from the frmInboundData and this data is also sent to the worksheet InboundData.

Using Excel '97 & Lotus Notes 5.0.11

Thanks,

[phendrena]

I've come across this code, which does work nicely and sends the email.
However, how do i get the Data from the user form into the email?

[vba]Dim objNotesSession As Object
Dim objNotesMailFile As Object
Dim objNotesDocument As Object
Dim objNotesField As Object
Function sendmail()
On Error GoTo SendMailError
EMailSendTo = "" '' Required - Send to address
EMailCCTo = "" '' Optional
EMailBCCTo = "" '' Optional
''Establish Connection to Notes
Set objNotesSession = CreateObject("Notes.NotesSession")
''Establish Connection to Mail File
'' .GETDATABASE("SERVER", "FILE")
Set objNotesMailFile = objNotesSession.GETDATABASE("", "")
''Open Mail
objNotesMailFile.OPENMAIL
''Create New Memo
Set objNotesDocument = objNotesMailFile.CREATEDOCUMENT
''Create 'Subject Field'
Set objNotesField = objNotesDocument.APPENDITEMVALUE("Subject", EMailSubject)
''Create 'Send To' Field
Set objNotesField = objNotesDocument.APPENDITEMVALUE("SendTo", EMailSendTo)
''Create 'Copy To' Field
Set objNotesField = objNotesDocument.APPENDITEMVALUE("CopyTo", EMailCCTo)
''Create 'Blind Copy To' Field
Set objNotesField = objNotesDocument.APPENDITEMVALUE("BlindCopyTo", EMailBCCTo)
''Create 'Body' of memo
Set objNotesField = objNotesDocument.CREATERICHTEXTITEM("Body")
With objNotesField
.APPENDTEXT "This e-mail is generated by an automated process."
.ADDNEWLINE 1
.APPENDTEXT "Please follow established contact procedures should you have any questions."
.ADDNEWLINE 2
End With
''Attach the file --1454 indicate a file attachment
''objNotesField = objNotesField.EMBEDOBJECT(1454, "", "C:\Temp\test.xls")
''objNotesField = objNotesField.EMBEDOBJECT(1454, "", ActiveWorkbook.FullName)
''Send the e-mail
objNotesDocument.send (0)
''Release storage
Set objNotesSession = Nothing
Set bjNotesSession = Nothing
Set objNotesMailFile = Nothing
Set objNotesDocument = Nothing
Set objNotesField = Nothing
''Set return code
sendmail = True
Exit Function
SendMailError:
Msg = "Error # " & Str(Err.Number) & " was generated by " _
& Err.Source & Chr(13) & Err.Description
MsgBox Msg, , "Error", Err.HelpFile, Err.HelpContext
sendmail = False
End Function[/vba]

Source : http://www.bygsoftware.com/Excel/VBA...otus_notes.htm

Thanks,

[MagicMike]

You can modify this code that I found on the net and use in my app.

[vba] Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

strbody = 'Place the body of the text here within quotations ""

On Error Resume Next
With OutMail
.To = 'email addy here
.CC = 'email addy here
.BCC = 'email addy here
.Subject = 'Subject here in quotation marks ""
.Body = strbody
.Send '.Send to send without seeing OR use .Display to display the outlook window
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing[/vba]

This sends an email from whoever is logged in on the machine. Hope this helps

Regards,

Mike

[phendrena]

Thanks for that Mike.
Your code appears to still refer to Outlook rather than notes.
The code that i found in the second post works well when using Lotus Notes, the problem now is how to get the data from the user form (and formatted) into the body of the email that is sent.

Any suggestions?

Thanks,

[CreganTur]

I've come across this code, which does work nicely and sends the email.
However, how do i get the Data from the user form into the email?

I imagine that your code would be triggered by an event on your UserForm. While your UserForm is still loaded, you can get the values of all the objects by refering to them like this:
[VBA]Me.txtboxName[/VBA]
Where txtboxName is the object's actual name- be sure that when you create UserForms you are giving your object unique names. Leaving the default names is bad practice.

[phendrena]

Yeah, they are setup with the Me.Value.
How do i use those values in the code that i've posted?
I am very new to VBA so please forgive my ignorance.

This is where the body of the email is set, i've added in the Me.Value lines but VBA gives a compile error (Invalid use of Me keyword).

[VBA]Set objNotesField = objNotesDocument.CREATERICHTEXTITEM("Body")
With objNotesField
Me.txtDate.Value
Me.txtCM.Value
Me.cboWhoCalled.Value
Me.txtDealerNo.Value
Me.txtContact.Value
Me.txtDealerName.Value
Me.cboReason.Value
Me.txtComments.Value
End With
[/VBA]

Please help.

Thanks,

[CreganTur]

This is where the body of the email is set, i've added in the Me.Value lines but VBA gives a compile error (Invalid use of Me keyword).

In your code above (that you stated worked), you can use something like this:
[VBA]With objNotesField
.APPENDTEXT "This e-mail is generated by an automated process."
.ADDNEWLINE 1
.APPENDTEXT "Please follow established contact procedures should you have any questions."
.ADDNEWLINE 2
APPENDTEXT Me.txtDate.Value & Me.txtCM.Value & Me.cboWhoCalled.Value _
& Me.txtDealerNo.Value & Me.txtContact.Value & Me.txtDealerName.Value _
& Me.cboReason.Value & Me.txtComments.Value
.ADDNEWLINE 3
End With
[/VBA]

When you run this you'll see that all of the values are smashed together. You'll need to format them. If you want just a space between them you can use something like:

[VBA]Me.txtDate.Value & " " & Me.txtCM.Value[/VBA]

HTH

[phendrena]

Thanks but i'm still getting the same compile error
If it makes a difference the sendmail code is added as a module and then called via a sub.

[CreganTur]

Thanks but i'm still getting the same compile error
If it makes a difference the sendmail code is added as a module and then called via a sub.

Then you need to gather all of the data from your Form objects into a string variable in the code behind the Form, then pass that string variable to your e-mail Sub so you can reference the string varaible as a part of the e-mail's body.

[phendrena]

Then you need to gather all of the data from your Form objects into a string variable in the code behind the Form, then pass that string variable to your e-mail Sub so you can reference the string varaible as a part of the e-mail's body.

That went right over my head tbh
Again my utter lack of proper vba knowledge shows with this one.
Would you be able to point me in the right direction for how to do this please.

Thanks,

[phendrena]

Alright, forget my last post.
I've moved the code from a module and added it as a sub and it now works as it should do. Is there anyway that i can create additional formatting (bold, coloured text etc) in the email that is sent or am i just stuck with plain text?

Thanks,

[phendrena]

Ok, other than the formatting i'm happy that this query is solved.