VBA to Send or Display Email

F

floggingmolly

Board Regular
Joined
Sep 14, 2019
Messages
167
Office Version
  1. 365
Platform
  1. Windows
I have a code that sends an email with pdf attachment. I have a dropdown in cell L3 with options Display and Send. Depending on what is chosen I want to either Display or Send the email. I can't seem to get the code to work. Any help would be appreciated. Below is the email section of my code.

Code: 
If .Range("K3").Value = "Email" Then
                                                  Set OutApp = CreateObject("Outlook.Application") 'Create Outlook Application
                                                  Set OutMail = OutApp.CreateItem(0) 'Create Email
                                                  With OutMail
                                                      .bcc = Sheet1.Range("R" & CustRow).Value & ";" & Sheet1.Range("S" & CustRow).Value & ";" & Sheet1.Range("T" & CustRow).Value
                                                      .Subject = "Notification email for " & Sheet1.Range("F" & CustRow).Value
                                                      .Body = "DO NOT RESPOND TO THIS EMAIL" & vbCrLf & _
                                                      "RESPOND TO THE SPECIALIST ASSIGNED TO YOUR AREA" _
                                                      & vbCrLf & "MARY - CENT/EAST, JAME - SOUTH/WEST/MIDA/GULF." _
                                                      & vbCrLf & vbCrLf & "This is line 1 of the email" _
                                                      & vbCrLf & "This is line 2 of the email" _
                                                      & vbCrLf & "This is line 3 of the email" _
                                                      & vbCrLf & Sheet1.Range("L" & CustRow).Value & " And here is line 4."
                                                      
                                                      
                                                      .Attachments.Add FileName
                                                      If .Range("L3").Value = "Display" Then
                                                      .Display
                                                      
                                                      Else:
                                                      .send
                                                      End If
                                                      
                                                     End With
                                                     
                                                      Application.Wait (Now + TimeValue("0:00:5"))
                                                  
                                    Else:
                                           
                                           
                                          WordDoc.Close False
                                          
                                    End If
                       
            End If '3 condition met
        Next CustRow
        WordApp.Quit
End With
End Sub
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Sort by date Sort by votes
At the moment your
Code: 
.Range("L3").Value = "Display"
is referencing the
Code: 
With OutMail
line and not the worksheet that the original With statement that
Code: 
.Range("K3").Value = "Email" Then
refers to.

Hardcode the sheet name to
Code: 
.Range("L3").Value = "Display"
 
Upvote 0
MARK858 said:
At the moment your
Code: 
.Range("L3").Value = "Display"
is referencing the
Code: 
With OutMail
line and not the worksheet that the original With statement that
Code: 
.Range("K3").Value = "Email" Then
refers to.

Hardcode the sheet name to
Code: 
.Range("L3").Value = "Display"
Click to expand...

I'm sorry, I'm fairly new to this so I don't follow what you're saying. There is a drop down in K3 with options of SAVE or EMAIL. Then in L3 I want to be able to choose DISPLAY or SEND the email. So if they choose SEND it will send without displaying.
 
Last edited:
Upvote 0
Post your full code as I can't see the line with the sheet name which is above the code that you have posted.
 
Upvote 0
Code: 
Sub CreateWordDocuments()
Dim CustRow, CustCol, LastRow, TemplRow, DaysSince, FrDays, ToDays As Long
Dim DocLoc, TagName, TagValue, TemplName, FileName As String
Dim CurDt, LastAppDt As Date
Dim WordDoc, WordApp, OutApp, OutMail As Object
Dim WordContent As Word.Range
With Sheet1
  
  If .Range("B3").Value = Empty Then
    MsgBox "Please select a correct template from the drop down list"
    .Range("G3").Select
    Exit Sub
  End If
    TemplRow = .Range("B3").Value 'Set Template Row
    TemplName = .Range("G3").Value 'Set Template Name
    FrDays = .Range("N3").Value 'Set From Days
    ToDays = .Range("P3").Value 'Set To Days
    DocLoc = Sheet2.Range("F" & TemplRow).Value 'Word Document Filename
    
    'Open Word Template
    On Error Resume Next 'If Word is already running
    Set WordApp = GetObject("Word.Application")
    If Err.Number <> 0 Then
    'Launch a new instance of Word
    Err.Clear
    'On Error GoTo Error_Handler
    Set WordApp = CreateObject("Word.Application")
    WordApp.Visible = True 'Make the application visible to the user
    
    
    End If
    
    
    LastRow = .Range("E500").End(xlUp).Row  'Determine Last Row in Table
        For CustRow = 8 To LastRow
                DaysSince = .Range("O" & CustRow).Value
                If TemplName <> .Range("O" & CustRow).Value And DaysSince >= FrDays And DaysSince <= ToDays Then
                                Set WordDoc = WordApp.Documents.Open(FileName:=DocLoc, ReadOnly:=False) 'Open Template
                                For CustCol = 1 To 13 'Move Through 9 Columns
                                    TagName = .Cells(7, CustCol).Value 'Tag Name
                                    TagValue = .Cells(CustRow, CustCol).Value 'Tag Value
                                     With WordDoc.Content.Find
                                        .Text = TagName
                                        .Replacement.Text = TagValue
                                        .Wrap = wdFindContinue
                                        .Execute Replace:=wdReplaceAll 'Find & Replace all instances
                                     End With
                                Next CustCol
                        
                        If .Range("I3").Value = "PDF" Then
                                       FileName = ThisWorkbook.Path & "\pdf files\" & "\" & .Range("F" & CustRow).Value & "_" & .Range("K" & CustRow).Value & ".pdf" 'Create full filename & Path with current workbook location, Last Name & First Name
                                       WordDoc.ExportAsFixedFormat OutputFileName:=FileName, ExportFormat:=wdExportFormatPDF
                                                                                                                    
                                       WordDoc.Close False
                                       
                                   Else: 'If Word
                                       FileName = ThisWorkbook.Path & "\word files\" & "\" & .Range("F" & CustRow).Value & "_" & .Range("K" & CustRow).Value & ".docx"
                                       WordDoc.SaveAs FileName
                                       Application.Wait (Now + TimeValue("0:00:05"))
                                     
                                   End If
                                   .Range("p" & CustRow).Value = TemplName 'Template Name
                                   .Range("q" & CustRow).Value = Now
                                    If .Range("K3").Value = "Email" Then
                                                  Set OutApp = CreateObject("Outlook.Application") 'Create Outlook Application
                                                  Set OutMail = OutApp.CreateItem(0) 'Create Email
                                                  With OutMail
                                                      .bcc = Sheet1.Range("R" & CustRow).Value & ";" & Sheet1.Range("S" & CustRow).Value & ";" & Sheet1.Range("T" & CustRow).Value
                                                      .Subject = "Notification email for " & Sheet1.Range("F" & CustRow).Value
                                                      .Body = "DO NOT RESPOND TO THIS EMAIL" & vbCrLf & _
                                                      "RESPOND TO THE SPECIALIST ASSIGNED TO YOUR AREA" _
                                                      & vbCrLf & "MARY - CENT/EAST, JAME - SOUTH/WEST/MIDA/GULF." _
                                                      & vbCrLf & vbCrLf & "This is line 1 of the email" _
                                                      & vbCrLf & "This is line 2 of the email" _
                                                      & vbCrLf & "This is line 3 of the email" _
                                                      & vbCrLf & Sheet1.Range("L" & CustRow).Value & " And here is line 4."
                                                      
                                                      
                                                      .Attachments.Add FileName
                                                      If .Range("L3").Value = "Display" Then
                                                      .Display
                                                      
                                                      Else:
                                                      .send
                                                      End If
                                                      
                                                     End With
                                                     
                                                      Application.Wait (Now + TimeValue("0:00:5"))
                                                  
                                    Else:
                                           
                                           
                                          WordDoc.Close False
                                          
                                    End If
                       
            End If '3 condition met
        Next CustRow
        WordApp.Quit
End With
End Sub
 
Upvote 0
Try...

Code: 
                    With OutMail
                        .bcc = Sheet1.Range("R" & CustRow).Value & ";" & Sheet1.Range("S" & CustRow).Value & ";" & Sheet1.Range("T" & CustRow).Value
                        .Subject = "Notification email for " & Sheet1.Range("F" & CustRow).Value
                        .Body = "DO NOT RESPOND TO THIS EMAIL" & vbCrLf & _
                                "RESPOND TO THE SPECIALIST ASSIGNED TO YOUR AREA" _
                              & vbCrLf & "MARY - CENT/EAST, JAME - SOUTH/WEST/MIDA/GULF." _
                              & vbCrLf & vbCrLf & "This is line 1 of the email" _
                              & vbCrLf & "This is line 2 of the email" _
                              & vbCrLf & "This is line 3 of the email" _
                              & vbCrLf & Sheet1.Range("L" & CustRow).Value & " And here is line 4."
                                                      
                                                      
                        .Attachments.Add FileName
                        If [COLOR="#FF0000"]Sheet1.[/COLOR]Range("L3").Value = "Display" Then
                            .Display
                                                      
                        Else:
                            .send
                        End If
                                                      
                    End With
 
Last edited:
Upvote 0
Thank you so much for your help. It looks like that is going to work. I really appreciate your quick response. :cool: I am learning slowly.
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Question Question
VBA run code based off cell value
Replies
1
Views
682
igold
igold
F
  • Question Question
VBA code giving error on line .Wrap = wdFindContinue
Replies
2
Views
1K
NdNoviceHlp
NdNoviceHlp
F
  • Question Question
VBA to send emails
Replies
1
Views
1K
floggingmolly
F
S
  • Solved
Tweak to VBA email code
Replies
3
Views
713
rollis13
rollis13
L
  • Locked
  • Question Question
How to stop VBA from creating pdf from drop-down list if the value is empty?
Replies
1
Views
279
Fluff
Fluff

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top