Dear Team,
I am using this bellow code for sending email through excel.
Code
Sub Weekly_Update_Mail()
Dim olApp As Object
Dim olMail As Object
Dim olInsp As Object
Dim wdDoc As Object
Dim oRng As Object
Dim sPath As String
Dim sMessage As String
Dim sFile As String
Dim cell As Range
Dim sBCC As String
Dim lngCount As Long
Dim diaFolder As FileDialog
Set diaFolder = Application.FileDialog(msoFileDialogFolderPicker)
sMessage = "<br>" & "<font size=""3"" face=""Cambria"" color=""Blue"">" & "Dear Valuable Customer, <br><br>" _
& "Greetings From " & "<b> SCHWING STETTER !!</b>" & "<br><br>" _
& " This is regarding " & "<b> SSI EQUIPMENT UPDATES </b>" & " which includes Product Details, Training programs which clearly reflects the Model Designs and changes that we have renovated in our equipment for the convenience Of the customer to get oriented about the Products. <br><br>" _
& " Kindly find the " & "<b> SCHWING STETTER PRODUCT UPDATES.</b>" & "<br><br>" _
& " Your feedback is very important as we are constantly looking for ways to improve our services and products.<br><br>" _
& " Stay Safe! Stay Healthy!!<br><br>" _
'create BCC list
For Each cell In Columns("I").Cells.SpecialCells(xlCellTypeConstants)
If cell.Value Like "?*@?*.?*" Then
sBCC = sBCC & ";" & cell.Value
Set olApp = CreateObject("Outlook.Application")
End If
Next cell
On Error Resume Next
Set olMail = olApp.CreateItem(0)
With olMail
.BCC = sBCC
.Subject = "SSI PRODUCT UPDATES AND PROGRAMS!!!!!!"
.HTMLBody = sMessage
Set olInsp = .GetInspector
Set wdDoc = olInsp.WordEditor 'access the message body for editing
.Display 'required to edit message body
End With
With Application.FileDialog(msoFileDialogOpen)
.AllowMultiSelect = True
.Show
' Display paths of each file selected
For lngCount = 1 To .SelectedItems.Count
attFilePath = Application.FileDialog(msoFileDialogOpen).SelectedItems(lngCount)
olMail.Attachments.Add (attFilePath)
Next lngCount
End With
Set olApp = Nothing
Set olMail = Nothing
Set olInsp = Nothing
Set wdDoc = Nothing
Set oRng = Nothing
End Sub
Display More
Now my code is open temp folder directly. But f i want to open ( Environ("USERPROFILE") & "\Desktop\Schwing product catalogue\" ) and from this folder the PDF file only to be attached by selecting manually file name. Other file format are not attach on my mail
Can any one help me how to do this