Email VBA Woes (1 Viewer)

[KevlarKev]

Hi all,

First up let me thank everyone who is on this forum for thier total commitment to hepling novices myself create functional databases. You have all been a great help over and over again.

However, I now need to ask for assitance again from someone who has the time to help me.

I am trying to create a vba script which I will attach to the on click event of a button. The script will open an email in outlook and populate the 'to' section with a email address pulled from a textbox on a form (which is based on a query called qryClients) and the body of the email would be generated from a free type memo field based on the same form.

I know that there used to be a 'sendobject' command in 2007 which allowed emails but I have searched for this in 2010 and it no longer exists?

Also, I have done a lot of research on this but the code involved looks incredibly complex to me (VBA noob) and would appreciate someone pointing out any obvious cockups ive made as its currently not running.

This is my code:

Private Sub Command12_Click()
Sub GenerateEmail(Recipient As String, subj As String, msg As String, attmt As String)
'Generate email
Dim OutApp As Object
Dim OutMail As Object
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With OutMail
.To = (I am assuming this should point to the text box on the form?
.CC = ""
.BCC = ""
.Subject = "Project Update"
.Body = (as above, pointing to the memo field?)
.Display
.Send
End With
On Error GoTo 0
End Sub

Any help would be very appreciated!!!

 

[pbaldy]

I'm pretty sure SendObject still exists. In the code above:

.To = Forms!FormName.TextboxName

Also, I'd either display or send, not both. You also start the sub twice, which will error.

 

[KevlarKev]

Thanks pbaldy, I am trying to figure this out as well I promise!

I made the changes you suggested. When I click on the button it comes up with an error in the VBA editor:

Compile Error Expected End Sub

It then highlights the top script of the VBA code in the debugger which is:

Private Sub Command12_Click()

Im guessing that I have 1 too many end subs?

This is my current code now i removed the 'send' command and stuck with the 'display'.

Private Sub Command12_Click()
Sub GenerateEmail(Recipient As String, subj As String, msg As String, attmt As String)
'Generate email
Dim OutApp As Object
Dim OutMail As Object
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With OutMail
.To = Forms!frmAddNewProjectNote.emailtest
.CC = ""
.BCC = ""
.Subject = "Project Update"
.Body = Forms!frmAddNewProjectNote.ProjectNote
.Display
End With
On Error GoTo 0
End Sub

Sorry to bother you

 

[pbaldy]

No, you still start it twice. Drop the line that starts with

Sub GenerateMail

 

[Sketchin]

This is what I did to email a PDF:

Private Sub CmdEmailPDF_Click()
On Error GoTo CmdEmailPDF_Click_Err
Dim Subject As String
Dim MyFileName As String
    Subject = Me.txtInvoiceNumber & "-" & "Invoice" & ".pdf"
    MyFileName = Me.txtInvoiceNumber & "_" & "Invoice" & ".pdf"

    ' Open Invoice form in preview mode
    DoCmd.OpenForm "YourFormName", acNormal, , "[InvoiceID]= forms!frmInvoicing![InvoiceID]"
    DoCmd.SendObject acForm, "YourFormName", "PDFFormat(*.pdf)", "", CC, "", Subject, "Hello,  attached you will find a PDF of your current invoice.", True, ""
    ' Close the form
    
    DoCmd.Close acForm, "YourFormName", acSaveNo
CmdEmailPDF_Click_Exit:
    Exit Sub
CmdEmailPDF_Click_Err:
    MsgBox Error$
    Resume CmdEmailPDF_Click_Exit
End Sub

 

[KevlarKev]

pbaldy you are a legend amongst men.

Thankyou very much, the code is now working sweetly!!!

 

[pbaldy]

Happy to help!