Issue with automatic emails showing without subject and recipients

Bering

Board Regular
Joined
Aug 22, 2018
Messages
125
Office Version
  1. 2016
Platform
  1. Windows
Hello,

I am facing a weird issue with the code below that I use to create automatic emails with attachment in Outlook.
In a worksheet there is a drop-down list with 48 items.
Items in this list determine the text of a letter contained in the same sheet (through vlookups and concatenations) and, at the same, all the details of the corresponding email (recipients, subject, attachment name and body), which is located in the Activesheet.
The macro loops through all the items in the drop-down list, creates 48 pdfs along with 48 emails with the corresponding pdf as an attachement. The email appears shortly and than gets saved as draft and closed.
The problem is that for only the last 6 emails or so, the emails gets correctly created with body, signature and attachment but without recipients and subject.
I tried removing all the other recipients but those 6 to test and it worked fine. I believe the looping works correctly, otherwise the email and the pdf would not be created, so I am stuck. Could this be a memory problem?
Has anyone experienced something similar or has an idea of what could be causing the issue? Thank you so much.

VBA Code:
Option Explicit

Sub CallLetterToPDF()
Dim FolderName As String, fName As String
Dim inputRange As Range, r As Range, c As Range
Dim first As Variant

Application.ScreenUpdating = False
''' Open file dialog and choose folder
With Application.FileDialog(msoFileDialogFolderPicker)
    .AllowMultiSelect = False
    If .Show = True Then
        FolderName = .SelectedItems(1) & "\"
        ActiveSheet.Range("AttachPath").Value = FolderName
    Else
        Exit Sub
    End If
End With

'''' Location of DataValidation cell
Set r = Worksheets("Call Letter").Range("C2")
'''' Get DataValidation values
Set inputRange = Evaluate(r.Validation.Formula1)

'''' Loop through DataValidation list

For Each c In inputRange
If first = "" Then first = c.Value
If c <> "" Then


    r.Value = c.Value
    fName = c.Value
   
    Worksheets("Call Letter").ExportAsFixedFormat Type:=xlTypePDF, Filename:=FolderName & ActiveSheet.Range("AttachFileName"), _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

  Send_Email_With_Signature
  
End If
Next c
r = first

MsgBox ("Done")


Application.ScreenUpdating = True

End Sub


VBA Code:
Sub Send_Email_With_Signature()
 
    Dim objOutApp As Object, objOutMail As Object
    Dim strBody As String, strSig As String
    Dim strLocation, strFileName, strFileExt, pass As String
    Dim StrSignature As String, sPath As String


    
    Set objOutApp = CreateObject("Outlook.Application")
    Set objOutMail = objOutApp.CreateItem(0)
    
    On Error Resume Next
    
    With objOutMail
    
    
        'SET THE EMAIL CONDITIONS
        .To = ActiveSheet.Range("MailDestinataries")
        .CC = ActiveSheet.Range("CCMailDestinataries")
        .BCC = ""
        .Subject = ActiveSheet.Range("MailSubject")
        
        'ADD ATTACHMENTS
         strLocation = ActiveSheet.Range("AttachPath")
         strFileName = ActiveSheet.Range("AttachFileName")
         strFileExt = ActiveSheet.Range("AttachFileExt")
'
        .Attachments.Add strLocation & strFileName & strFileExt
        
        
        'IF SENT FROM ANOTHER EMAIL ACCOUNT (MUST ALREADY BE SETUP)
        .SentOnBehalfOfName = "xxxx@xxx.com"
        
        'CHECK NAMES, ENSURES INTERNAL EMAIL ADDRESSES EXISTS IN ADDRESS BOOK
        .Recipients.ResolveAll
        .Display


        'GET THE HTML CODE FROM THE SIGNATURE
        strSig = .HTMLbody
        
        'CONVERT BODY IN HTML
        
        ActiveSheet.Range("MailBody").Copy
        ActiveSheet.Range("G9").PasteSpecial Paste:=xlPasteValues
        ActiveSheet.Range("H9") = "=fnConvert2HTML(RC[-1])" '''this is a function that creates the HTML body of the email
        
        strBody = ActiveSheet.Range("H9")
        strBody = "<font style=""font-family: Raleway; font-size: 11pt;""/font>" & strBody
        
        'COMBINE THE EMAIL WITH THE SIGNATURE
        
        .HTMLbody = strBody & vbNewLine & vbNewLine & strSig
        
        'AUTOMATICALLY SAVE EMAIL AS DRAFT (IT WILL STILL BRIEFLY POPUP)
        .Save
        .Close 0
        ActiveSheet.Range("G9,H9").ClearContents
    
    End With
    
    On Error GoTo 0
    Set objOutMail = Nothing
    Set objOutApp = Nothing

End Sub
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Bering

Board Regular
Joined
Aug 22, 2018
Messages
125
Office Version
  1. 2016
Platform
  1. Windows
Hello,

thanks, yes it is getting populated and semi colon is also appearing after every email address.

This is driving me crazy.........



Hi

debug
.Subject = ActiveSheet.Range("MailSubject")
and check weather the subject line is getting populated or not.

for recipient check weather u have used semi colon after every email address like xyz@gmail.com;abc@gmail.com
 

Bering

Board Regular
Joined
Aug 22, 2018
Messages
125
Office Version
  1. 2016
Platform
  1. Windows
I have finally managed to fix this by specifying the property of all ranges e.g. Value.
Not sure why but it works properly now.

Thank you.


Hello,

thanks, yes it is getting populated and semi colon is also appearing after every email address.

This is driving me crazy.........
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,119,257
Messages
5,577,019
Members
412,762
Latest member
Philfy
Top