• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

VBA excel - Loop and directory|VBA & Macros|Excel Forum|My Online Training Hub

You are here: Home / VBA excel - Loop and directory|VBA & Macros|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumVBA & MacrosVBA excel - Loop and directory
sp_PrintTopic sp_TopicIcon
VBA excel - Loop and directory
Avatar
chantal arzur
Member
Members
Level 0
Forum Posts: 9
Member Since:
March 21, 2021
sp_UserOfflineSmall Offline
1
March 21, 2021 - 4:00 am
sp_Permalink sp_Print

Hi,

I am a bit unfamiliar with coding in VBA

I have followed one that was on this forum but I need to make a few adjustments

VBA to Create PDF from Excel Sheet & Email It With Outlook (myonlinetraininghub.com)

1- I want to put a specific folder instead of the VBA asking to select the folder when prompt

2- I want the VBA to loop so that it sends the full workbook with individual worksheet as PDF as per email on each worksheet instead of going to each worksheet and running the VBA 

I hope it makes sense

Appreciate any assistance in advance

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1807
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
2
March 26, 2021 - 4:37 pm
sp_Permalink sp_Print

Hi Chantal,

You can use the code below to send all sheets one by one:

Sub SendAllSheets()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
     ws.Activate
     EmailScript
Next
End Sub

The code loops through sheets and calls your EmailScript procedure.

Avatar
chantal arzur
Member
Members
Level 0
Forum Posts: 9
Member Since:
March 21, 2021
sp_UserOfflineSmall Offline
3
March 28, 2021 - 2:58 am
sp_Permalink sp_Print

Hi

Thank you for your information.

I tried to paste it after my emailscript but it loops on the same page. How do I ask to move to the next worksheet until all of them are sent?

Also, what is the path format to automatically save in a particular folder?

 

thanks again

---------------------------------Script-------------------------------

Sub create_and_email_pdf()
' Create a PDF from the current sheet and email it as an attachment through Outlook

Dim EmailSubject As String, EmailSignature As String

Dim CurrentMonth As String, DestFolder As String, PDFFile As String
Dim Email_To As String, Email_CC As String, Email_BCC As String
Dim OpenPDFAfterCreating As Boolean, AlwaysOverwritePDF As Boolean, DisplayEmail As Boolean
Dim OverwritePDF As VbMsgBoxResult
Dim OutlookApp As Object, OutlookMail As Object
Dim ws As Worksheet

CurrentMonth = ""

'Prompt for file destination
With Application.FileDialog(msoFileDialogFolderPicker)

If .Show = True Then
DestFolder = .SelectedItems(1)
Else

MsgBox "You must specify a folder to save the PDF into." & vbCrLf & vbCrLf & "Press OK to exit this macro.", vbCritical, "Must Specify Destination Folder"
Exit Sub
End If
End With

'Current month/year stored in H6 (this is a merged cell)
CurrentMonth = ActiveSheet.Range("H1").Value

'Create new PDF file name including path and file extension
PDFFile = DestFolder & Application.PathSeparator & ActiveSheet.Name _
& "_" & CurrentMonth & ".pdf"

'Create the PDF
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDFFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=OpenPDFAfterCreating

'Create an Outlook object and new mail message
Set OutlookApp = CreateObject("Outlook.Application")
Set OutlookMail = OutlookApp.CreateItem(0)

'Display email and specify To, Subject, etc
With OutlookMail

.Display
.To = Email_To
.CC = Email_CC
.BCC = Email_BCC
.Subject = ActiveSheet.Name & EmailSubject & CurrentMonth
.Attachments.Add PDFFile
.Body = "Dear Customer," & vbNewLine & vbNewLine & "Please find attached your latest statement." & vbNewLine & vbNewLine & "Kindest Regards" & vbNewLine & vbNewLine & "West Pharmaceutical Services, Inc." & vbNewLine & "www.westpharma.com"

If DisplayEmail = False Then
.Send

End If

End With

For Each ws In ThisWorkbook.Worksheets
ws.Activate
EmailScript
Next
End Sub

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1807
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
4
March 28, 2021 - 3:06 am
sp_Permalink sp_Print

Please paste the code provided UNDER the End Sub line of your existing code, NOT inside the procedure you have.

The code you have pasted above does not match to the one you have in the file attached at your first message.

Sub SendAllSheets()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
     ws.Activate
     create_and_email_pdf
Next
End Sub

I updated the red part to call your existing procedure.

 

To change the DestFolder, remove this part of code:

'Prompt for file destination
With Application.FileDialog(msoFileDialogFolderPicker)

If .Show = True Then
DestFolder = .SelectedItems(1)
Else

MsgBox "You must specify a folder to save the PDF into." & vbCrLf & vbCrLf & "Press OK to exit this macro.", vbCritical, "Must Specify Destination Folder"
Exit Sub
End If
End With

 

Use instead of the code above:

DestFolder = "C:\Test Folder" (update to the static path you need)

Avatar
chantal arzur
Member
Members
Level 0
Forum Posts: 9
Member Since:
March 21, 2021
sp_UserOfflineSmall Offline
5
March 28, 2021 - 3:24 am
sp_Permalink sp_Print

awesome. thank you so much.

one last question if you don't mind.

How do I code to have the email sent automatically? At the moment it creates the email and I need to press on SEND.

 

Again thank you so much for your help

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1807
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
6
March 28, 2021 - 3:43 am
sp_Permalink sp_Print

Replace:

With OutlookMail

.Display

with:

With OutlookMail

.Send

And remove this part:

If DisplayEmail = False Then
.Send

End If

Avatar
chantal arzur
Member
Members
Level 0
Forum Posts: 9
Member Since:
March 21, 2021
sp_UserOfflineSmall Offline
7
March 28, 2021 - 4:25 am
sp_Permalink sp_Print sp_EditHistory

thank you. I had to make a change to make it work but thank you so much.

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Philip Treacy, Velouria, Dario Serrati
Guest(s) 11
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
yashal minahil
Oluwadamilola Ogun
Yannik H
dectator mang
Francis Drouillard
Orlando Inocente
Jovitha Clemence
Maloxat Axmatovna
Ricardo Freitas
Marko Meglic
Forum Stats:
Groups: 3
Forums: 24
Topics: 6200
Posts: 27182

 

Member Stats:
Guest Posters: 49
Members: 31861
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.