Hi all,
Wandering if anyone can point me in the right direction.
I created a vb userform for people to input data.
It is basically a bunch of fields, then the vba macro will create an email with some pre-defined settings, and then put the data in the email as a table and then send to pre-defined email address.
But now what I want to do is that instead of creating a table, I want the data to be stored in an excel, and send that excel as attachment.
Anyone can advice on how to do this? (ideally no need to create / save a temporary workbook)
Thanks in advance.
Below is my original code
Wandering if anyone can point me in the right direction.
I created a vb userform for people to input data.
It is basically a bunch of fields, then the vba macro will create an email with some pre-defined settings, and then put the data in the email as a table and then send to pre-defined email address.
But now what I want to do is that instead of creating a table, I want the data to be stored in an excel, and send that excel as attachment.
Anyone can advice on how to do this? (ideally no need to create / save a temporary workbook)
Thanks in advance.
Below is my original code
VBA Code:
Private Sub CommandButton1_Click()
Dim OL As Object, MailSendItem As Object
Set OL = CreateObject("Outlook.Application")
Set MailSendItem = OL.CreateItem(plMailItem)
With MailSendItem
.Subject = "(Confidential) Request"
.to = [EMAIL]xxxx@xxxx.com[/EMAIL]
.cc = ccemail.Value
.htmlbody = "<html><body>" _
& " From: " & ddlBr.SelText _
& " <br>CC: " & ccemail.Value _
& " <br>Contact Person: " & ContactName.Value _
& " <br>Contact Number: " & ContactNo.Value _
& " <br><br><br><table border=1 cellspacing=0 cellpadding=0><tr><td width=130><p align=center>Date of Document (From)</td><td width=130><p align=center>Date of Document (To)</td><td width=150><p align=center>A/C Name</td><td width=110><p align=center>A/C No.</td><td width=150><p align=center>Document Type</td><td width=150><p align=center>Delivery method</td><td width=150><p align=center>Particulars</td></tr>" _
& " <tr><td><p align=center>" & Trim(date1.Value) & " </td><td><p align=center>" & Trim(dateto1.Value) & " </td><td><p align=center>" & Trim(accname1.Value) & " </td><td><p align=center>" & accno1.Value & " </td><td><p align=center>" & ComboBox1.SelText & " </td><td><p align=center>" & ComboBox9.SelText & " </td><td><p align=center>" & part1.Value & " </td></tr>" _
& " <tr><td><p align=center>" & Trim(date2.Value) & " </td><td><p align=center>" & Trim(dateto2.Value) & " </td><td><p align=center>" & Trim(accname2.Value) & " </td><td><p align=center>" & accno2.Value & " </td><td><p align=center>" & ComboBox2.SelText & " </td><td><p align=center>" & ComboBox10.SelText & " </td><td><p align=center>" & part2.Value & " </td></tr>" _
& " <tr><td><p align=center>" & Trim(date3.Value) & " </td><td><p align=center>" & Trim(dateto3.Value) & " </td><td><p align=center>" & Trim(accname3.Value) & " </td><td><p align=center>" & accno3.Value & " </td><td><p align=center>" & ComboBox3.SelText & " </td><td><p align=center>" & ComboBox11.SelText & " </td><td><p align=center>" & part3.Value & " </td></tr>" _
& " <tr><td><p align=center>" & Trim(date4.Value) & " </td><td><p align=center>" & Trim(dateto4.Value) & " </td><td><p align=center>" & Trim(accname4.Value) & " </td><td><p align=center>" & accno4.Value & " </td><td><p align=center>" & ComboBox4.SelText & " </td><td><p align=center>" & ComboBox12.SelText & " </td><td><p align=center>" & part4.Value & " </td></tr>" _
& " <tr><td><p align=center>" & Trim(date5.Value) & " </td><td><p align=center>" & Trim(dateto5.Value) & " </td><td><p align=center>" & Trim(accname5.Value) & " </td><td><p align=center>" & accno5.Value & " </td><td><p align=center>" & ComboBox5.SelText & " </td><td><p align=center>" & ComboBox13.SelText & " </td><td><p align=center>" & part5.Value & " </td></tr>" _
& " <tr><td><p align=center>" & Trim(date6.Value) & " </td><td><p align=center>" & Trim(dateto6.Value) & " </td><td><p align=center>" & Trim(accname6.Value) & " </td><td><p align=center>" & accno6.Value & " </td><td><p align=center>" & ComboBox6.SelText & " </td><td><p align=center>" & ComboBox14.SelText & " </td><td><p align=center>" & part6.Value & " </td></tr>" _
& " <tr><td><p align=center>" & Trim(date7.Value) & " </td><td><p align=center>" & Trim(dateto7.Value) & " </td><td><p align=center>" & Trim(accname7.Value) & " </td><td><p align=center>" & accno7.Value & " </td><td><p align=center>" & ComboBox7.SelText & " </td><td><p align=center>" & ComboBox15.SelText & " </td><td><p align=center>" & part7.Value & " </td></tr>" _
& " <tr><td><p align=center>" & Trim(date8.Value) & " </td><td><p align=center>" & Trim(dateto8.Value) & " </td><td><p align=center>" & Trim(accname8.Value) & " </td><td><p align=center>" & accno8.Value & " </td><td><p align=center>" & ComboBox8.SelText & " </td><td><p align=center>" & ComboBox16.SelText & " </td><td><p align=center>" & part8.Value & " </td></tr>" _
& " </table>" _
& " <BR><table border=1 cellspacing=0 cellpadding=0><td colspan=7 width=1000><p align=left> Details of Document </td></tr>" _
& " <tr><td width=200><p align=center>Teller ID</td><td width=200><p align=center>Txn Br</td><td width=200><p align=center>Txn Amount</td><td width=200><p align=center>Cheque No.</td><td width=200><p align=center>Date of A/C Closure</td></tr>" _
& " <tr><td><p align=center>" & Trim(Textteller.Value) & " </td><td><p align=center>" & Trim(Textbr.Value) & " </td><td><p align=center>" & Textamt.Value & " </td><td><p align=center>" & Textcq.Value & " </td><td><p align=center>" & Textclosedate.Value & " </td></tr>" _
& " </table>" _
& " <br><br><table border=1 cellspacing=0 cellpadding=0><tr><td width=250><p align=center>Ref No.</td><td width=250><p align=center>Date Processed</td><td width=250><p align=center>Processed By</td><td width=250><p align=center>Checked by</td></tr>" _
& " <tr><td width=250 height=50><p align=left>.<p align=left></td><td width=250><p align=left>.</td><td width=250><p align=left>.</td><td width=250><p align=left>.</td></tr>" _
& " </table>" _
& " <BR><BR><BR>" _
& " [Confidential] "
.send
End With
MsgBox "Request sent successfully! A copy of this request can be found in your Outlook's Sent Items."
Unload Me
End Sub