Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Emailing a Word Document triggered by a cell selection in Excel

  1. #1
    New Member
    Join Date
    Jul 2018
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Emailing a Word Document triggered by a cell selection in Excel

    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

  2. #2
    Moderator Macropod's Avatar
    Join Date
    Aug 2007
    Location
    Canberra, Australia
    Posts
    2,851
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Emailing a Word Document triggered by a cell selection in Excel

    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).
    Cheers
    Paul Edstein
    [MS MVP - Word]

  3. #3
    New Member
    Join Date
    Jul 2018
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Emailing a Word Document triggered by a cell selection in Excel

    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

  4. #4
    Moderator Macropod's Avatar
    Join Date
    Aug 2007
    Location
    Canberra, Australia
    Posts
    2,851
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Emailing a Word Document triggered by a cell selection in Excel

    Try something along the lines of the following, which you would add to the relevant worksheet's code module.
    Code:
    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
    Cheers
    Paul Edstein
    [MS MVP - Word]

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •