Emailing a Word Document triggered by a cell selection in Excel

[Blanchetdb]

Hi,

I am looking to email a Word Document to an email address from Excel

the email address contained in the cell (D24) will change based on the client completing the form - The email is triggered by specific entry in a Cell (C26)

there would be a body to the email but also the Word document in attachment

is that possible?

thank you

 

[Macropod]

Yes, it's possible, but you'd need to specify the condition upon which the code could decide whether there's enough information in the workbook and the Word document to validate the sending (plus a valid email address).

 

[Blanchetdb]

this is what I would be dealing with:

Cell C36 has a drop down menu - the client makes a selection, depending on the selection (example.. Term) the Word document would be sent to a designated email address located in Cell D24.

The Word Document is a standard template checklist that the client needs depending on the selection chosen in Cell C36 - the email address would change from one request to another as it would be based on the address entered in Cell D24

 

[Macropod]

Try something along the lines of the following, which you would add to the relevant worksheet's code module.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Note: This code requires references to Outlook, via Tools|References
Application.ScreenUpdating = False
Dim StrDoc As String, StrEml As String
With ActiveSheet
  If Intersect(Target, .Range("C36")) Is Nothing Then Exit Sub
  StrDoc = Target.Value: StrEml = .Range("D24").Value
  If InStr(StrDoc, ".doc") = 0 Then Exit Sub
  If InStr(StrEml, "@") = 0 Then Exit Sub
End With
Dim olApp As Outlook.Application, olMail As Outlook.MailItem
On Error Resume Next
Set olApp = GetObject(, "Outlook.Application")
If olApp Is Nothing Then
  Set olApp = CreateObject("Outlook.Application")
  On Error GoTo 0
  If olApp Is Nothing Then
    MsgBox "Can't start Outlook.", vbExclamation
    GoTo ErrExit
  End If
End If
With olApp
  Set olMail = .CreateItem(olMailItem)
  With olMail
    .To = StrEml
    .CC = ""
    .BCC = ""
    .Subject = "Some Subject"
    .Attachments.Add StrDoc
    .Display
  End With
End With
ErrExit:
Set olMail = Nothing: Set olApp = Nothing
Application.ScreenUpdating = True
End Sub