Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
opening outlook and populating
I have the following code which is started with a userform button.
It puts together the recipient string of email addresses, generates a random subject line and message body, if the auto message is checked. i now need to open out look, populate the sendto, subject and message body with these things. Do i do this all in the same code? Should I build an email function? Any help would be appreciated. My current code is below and is the code fro the userform button Private Sub SendEmailBtn_Click() Dim CBCtrl As MSForms.Control Dim strReceipients As String Dim MsgBody As String 'get the email addresses from the check boxes For Each CBCtrl In RndmemailFrm.Controls 'If TypeOf CBCtrl Is MSForms.CheckBox Then If TypeName(CBCtrl) = "CheckBox" Then If CBCtrl.Object.Value = True Then If InStr(CBCtrl.Caption, "@") 0 Then strReceipients = strReceipients & ";" & CBCtrl.Caption End If End If End If strReceipients = Mid(strReceipients, 1) Next ' generate the random subject line from the table in column 2 SubLine = Cells(Rnd * (Cells(Rows.Count, 2).End(xlUp).Row - 1) + 2, 2) ' if the Use Automated message is checked, then randomly select a message from column 3 ' or else use the message in the message text box For Each CBCtrl In RndmemailFrm.Controls If CBCtrl.Tag = "AutoMess" Then If CBCtrl.Object.Value = True Then MsgBody = Cells(Rnd * (Cells(Rows.Count, 3).End(xlUp).Row - 1) + 2, 3) Else MsgBody = RndmemailFrm.MsgBdyTB.Text End If End If Next End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
opening outlook and populating
There are a number of ways of doing this. Here's a copy of a relatively
simple routine that I use on a daily basis: Sub MailIt() Dim olApp As Object Set olApp = CreateObject("Outlook.Application") Dim olNameSpace As Object Set olNameSpace = olApp.GetNameSpace("MAPI") Dim olFolder As Object Set olFolder = olNameSpace.GetDefaultFolder(6) Dim olMail As Object Set olMail = olApp.CreateItem(0) Dim sFileName As String sFileName = "C:\VB 2008\sales.csv" Dim sRecipient As String sRecipient = " With olMail .Subject = "the subject" .Recipients.Add sRecipient .Body = "body message" .Attachments.Add sFileName .Display .Send End With End Sub It seems you already have your own message body and recipient list, so just incorporate them into the code. The above code could either go into a separate sub procedure with the recipient & message from your procedure as inputs, or put it at the end of your existing procedure (probably simpler). There are quite few example procedures also available at: http://www.rondebruin.nl/sendmail.htm Hopefully this helps. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
populating the recipient address in outlook | Excel Programming | |||
populating a workbook but not opening it | Excel Programming | |||
populating table from outlook | Excel Discussion (Misc queries) | |||
Vba - Opening Outlook with Vba | Excel Programming | |||
VBA for opening file/populating combo box with sheet names | Excel Programming |