VBA Code to Open Outlook from a Database and start a new message (1 Viewer)

TylerTand

Registered User.
Local time
Today, 05:41
Joined
Aug 31, 2007
Messages
95
Ok, I can already do the hard part of starting Outlook and I can even create a new messge using sendkeys "^n".

Here is the code I have used so far:
Private Sub Label380_Click()
MyAppID = Shell("C:\Program Files\Microsoft Office\Office\OFFICE11\Outlook.exe", 1)

vartimer = Timer + 4 'This part is required to let outlook open completely before the send keys command can be sent to open the new message
Do Until Timer > vartimer
Loop

AppActivate MyAppID

SendKeys "^n" 'If I add my email address to this sendkeys it doesn't do anything but open the new message window.


End Sub



Now what I can't do. For some reason I can't use Send keys to put my email address in the address field or put any other data in the new message. Can someone help me figure out why sendkeys"" doesn't allow me to fill out the email address? I am pretty sure that the problem lies in that the focus is still on the db instead of the new message when the second send keys command is sent to add my email address. I can't figure out how to make the send keys wait for the new message to be opened before continuing to send the next part. Your help is greatly appreciated if you have any experience having done this.

Tyler
 

dhaval42

New member
Local time
Today, 05:41
Joined
May 22, 2008
Messages
2
Hi There,
You can add the same Do Until loop again to add the email address and use the Sendkeys again to send the email address for example

vartimer2 = Timer + 4
Do Until Timer > vartimer2
Loop
SendKeys "email address"

but the thing is the email address gets added in the main window rather than in the "To" section. This I am not able to figure out. If you can solve this then do let me know as well. I am also stuck here.. :eek:
 

dhaval42

New member
Local time
Today, 05:41
Joined
May 22, 2008
Messages
2
Adding email address in the "To" of a new mail using VBA

Hi There,
I have found out as to how to send the email address in the "To" box of a new email.:rolleyes: Instead of sending the email address as it is, send it with a Shift ("+") attached to it. The code for the same is as below

Dim ShiftKey As String
ShiftKey = "+" :eek:
SendKeys ShiftKey & "email address", False

Also instead of adding vartimer and Do loop, try the following code.

Application.Wait Now + TimeValue("00:00:04")
 

twoplustwo

Registered User.
Local time
Today, 05:41
Joined
Oct 31, 2007
Messages
507
Code:
Dim olLook As Object                     'Start MS Outlook
Dim olNewEmail As Object                 'New email in Outlook
Dim strContactEmail As String            'Contact email address
Dim strCustomer As String                'Customer Name
Dim strSite As String                    'Site Name
 
If IsNull(Me.lstContacts.Column(0)) Then
    MsgBox "No site-level contacts selected.", vbInformation, "Select contact"
    Exit Sub
End If
 
strContactEmail = Forms!frmSummerShut2008.lstContacts.Column(6)
If strContactEmail <> Me.lstComCtc.Column(6) Then
    iResponse = MsgBox("Selected site contact does not match the Primary contact. Continue sending?", vbYesNo, "Check contacts")
    If iResponse <> vbYes Then Exit Sub
End If
strCustomer = Me.CompanyName
For x = 1 To lstCompanies.ListCount
    If lstCompanies.Selected(x) = True Then
       strSite = strSite & lstCompanies.Column(3, x)
    End If
Next
 
Set olLook = CreateObject("Outlook.Application")
Set olNewEmail = olLook.createitem(0)
strEmailSubject = "**TEST** British Energy - Summer 2008 Consumption  - Request for Information"
strEmailText = "**TEST ** Company – Summer 2008 Shutdown Enquiry" & Chr$(13) & _
               Chr$(13) & "It is important for British Energy that we know what power our customers are likely to consume. During holiday periods when consumption patterns can change this becomes more difficult." & Chr$(13) & _
               Chr$(13) & "To assist us we are asking some of our larger customers to provide information about their intentions over the Summer Holiday period. We are particularly interested in the period 2oth July – 14 August 2008 but if you have a summer shutdown outside this period then please let us know." & Chr$(13) & _
               Chr$(13) & "Please complete the attached spreadsheet by Monday 1 July 2008 and return it to email@.com even if you do not expect your consumption to differ from your normal consumption pattern." & Chr$(13) & _
               Chr$(13) & "Details:" & " " & strCustomer & ": " & strSite & Chr$(13) & _
               Chr$(13) & "Please amend the contact details for queries relating to this information if the primary contact is different from that given below. Complete the details if any information is missing and return to email~@[EMAIL="forecasts@british-energy.com"].com[/EMAIL]" & Chr$(13) & _
               Chr$(13) & "Name:" & " " & strContactFirstName & " " & strContactSecondName & Chr$(13) & _
               Chr$(13) & "Position:" & " " & strContPosition & Chr$(13) & _
               Chr$(13) & "Telephone:" & " " & strContPhone & Chr$(13) & _
               Chr$(13) & "Email Address:" & " " & strContactEmail & Chr$(13) & _
               Chr$(13) & "The Demand Forecasting team thank you for your efforts." & Chr$(13) & Chr$(13)
 
   With olNewEmail   'Attach template
      .To = strContactEmail
      .CC = strCc
      .body = strEmailText
      .subject = strEmailSubject
      .attachments.Add ("S:\DirectSupply\Common\Demand Forecasting\AA_Forecasting\Special Day Load Management\Special Periods\Summer\2007\Campaign\Email template\Information Templatev3.xls")
      .display
   End With

I use the above to populate the email fields from outlook, attach a spreadsheet and tailor the email to companies/sites in the a listbox.
 

Users who are viewing this thread

Top Bottom