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

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