Posts by jiajiexue1985

I have set the signature as default, but I guess it's not translating into the process.

Thanks.

Quote from rory

It's much easier if you can use an Outlook template message with the picture already embedded.

Actually, I would like to take this 1 step further. I would like to have a company logo inserted in the email body rather than the email attachment as a file.

.Attachments.Add "W:\Business\BUSINESS OFFICE\CDD Logo.jpg" is where the logo located in the shared drive.

What do I need to do to make this happen?

Thank you.

Sub ExcelToOutlookSR()
Dim mApp As Object
Dim mMail As Object
Dim SendToMail As String
Dim MailSubject As String
Dim mMailBody As String
For Each r In Selection
SendToMail = Range("B" & r.Row)
SendCCMail = Range("D" & r.Row)
MailSubject = Range("E" & r.Row)
mMailBody = Range("H" & r.Row)
mAttachment = Range("I" & r.Row)
Set mApp = CreateObject("Outlook.Application")
Set mMail = mApp.CreateItem(0)
With mMail
.To = SendToMail
.CC = SendCCMail
.Subject = MailSubject
.Body = mMailBody
.Attachments.Add mAttachment
.Attachments.Add "W:\Business\BUSINESS OFFICE\CDD Logo.jpg"
.Display
.Send
End With
Next r
End Sub

Truly appreciate your time and help on this.

Quote from Carim

Pleased to hear you have managed to fix your problem

Thank you everyone, for your time on this.

I figured it out, turns out I need to remove the "=" on line 20

.Attachments.Add = mAttachment should be .Attachments.Add mAttachment (see Correction below)

Sub ExcelToOutlookSR()
Dim mApp As Object
Dim mMail As Object
Dim SendToMail As String
Dim MailSubject As String
Dim mMailBody As String
For Each r In Selection
SendToMail = Range("B" & r.Row)
SendCCMail = Range("D" & r.Row)
MailSubject = Range("E" & r.Row)
mMailBody = Range("H" & r.Row)
mAttachment = Range("I" & r.Row)
Set mApp = CreateObject("Outlook.Application")
Set mMail = mApp.CreateItem(0)
With mMail
.To = SendToMail
.CC = SendCCMail
.Subject = MailSubject
.Body = mMailBody
' Correction for the Specific instruction for the Attachment ''''''''''''''''''''  
.Attachments.Add mAttachment
.Display
End With
Next r
End Sub

haha, no worries. So the path i have is copied from the "Copy Path" located on the left upper corner of the folder.

Thanks.

"P:\CDD Business Office\Medical Form - HIPPA Safe\2022\12, December 2022\Individual File\Espinoza.docx"

Quote from Carim

Sorry but I cannot see what is exactly in front of you eyes ...

In order to 100 % sure ... you can use File Explorer ... it will show you the full path as well as the exact file name and the extension ...

Hi,

Thank you for your input. The path is formatted correctly, but I am still getting the error message.

Quote from Carim

Please feel free to comment ...

So Excel is telling me there is something wrong with the code, asking me to debug. Column I only has path, not file name. Can you please tell me what the format of the path should be, if it’s not just a path itself?

Thank you very much for your help.

Hello All,

I am trying to add attachment from path referenced in column I, but not working. Can anyone help me on this?

Thank you a million.

Sub ExcelToOutlookSR()
Dim mApp As Object
Dim mMail As Object
Dim SendToMail As String
Dim MailSubject As String
Dim mMailBody As String
For Each r In Selection
SendToMail = Range("B" & r.Row)
SendCCMail = Range("D" & r.Row)
MailSubject = Range("E" & r.Row)
mMailBody = Range("H" & r.Row)
mAttachment = Range("I" & r.Row)
Set mApp = CreateObject("Outlook.Application")
Set mMail = mApp.CreateItem(0)
With mMail
.To = SendToMail
.CC = SendCCMail
.Subject = MailSubject
.Body = mMailBody
.Attachments.Add = mAttachment
.Display
End With
Next r
End Sub

Quote from jolivanes

We want to make sure you have something to think about this coming weekend.

Code

Sub Or_Simply()
Dim sh As Worksheet
Application.ScreenUpdating = False
For Each sh In ActiveWorkbook.Worksheets
If sh.Name <> "Instructions" And sh.Visible = True Then
sh.PrintOut , , , , , True, , "C:\PDF Folder\" & sh.Name & ".PDF"    '<---- Change as required
End If
Next sh
Application.ScreenUpdating = True
End Sub

lol, thank you very much for all your help. This is extremely helpful.

Thanks.

Hi all,

I am trying to change location of the pdfs to different folder location instead of desktop. I do have hidden tabs.

Any help is much appreciated.

Sub Save_As_PDF_To_Desktop_4()
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
If sh.Name <> "Instructions" And sh.Visible = True Then sh.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
CreateObject("WScript.Shell").specialfolders("Desktop") & "\" & sh.Name & ".pdf" _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=False
Next sh
End Sub