VBA code to convert excel to pdf and email it as attachment

aarondesin91

New Member
Joined
Jun 23, 2013
Messages
7
Dear Forumers,

I really need your help. I am new to this whole VBA coding thing have no basic at all in programming and stuff so please help me out here. I am currently assigned a project where I have to create a excel sheet which act as a templete for sending request. The requirement of the project is that I need a vba code for a button when i click it, it will convert my active sheet alone to pdf, automatically save it with the title captured from a cell in the active sheet which is entered by the user. Email this pdf as a attachment to the specific person. Please help me out, my job depends on this project please guys out there.

Thank you
 
For it to be "proof" you would somehow need a copy of the email AFTER it is sent, so the code would need to wait for the user to finish working on the email, then pull it from the Sent mailbox in outlook, then save it.

A couple of easier methods would provide you the proof you need:


  1. You could simply add a .BCC to your code:
    Code:
        .BCC = "mySecureEmail@abc.com"
  2. You can create a rule in outlook that will take any email with, for example, "completed case review" in the body and either move a copy to another folder in outlook or forward a copy to a secure email of your choice

Both methods provide you full proof and if you use an external web based email to copy it to, then you can save yourself the local storage space as well.

If you just want to save a copy of the pdf you are attaching to a network drive, that is much simpler and we can definitely accomplish that, but that does not provide any proof the email was actually sent.:cool:
 
Upvote 0

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
I appreciate the other methods suggested. Unfortunately, I do need the pdf copy of the email AFTER it is sent to be saved on our Z drive. I would change the subject line of the email to "Completed Case Review". Every user will have a CutePDF Writer identified as a printer in their Outlook program, since not everybody has Adobe. If it is possible for the code to immediately access (after the user presses send) the last email with Completed Case Review in the subject line in their Sent mailbox in Outlook and save it as a pdf on the Z drive, please let me know. Otherwise, I may have to have the users manually take care of this, or bcc myself and I would take care of it for them. We have 500-600 of these annually, so automating it is definitely preferred.

If you have any more ideas, please let me know. I really appreciate your assistance. Thanks very much!:help:
 
Upvote 0
I'm sure it is possible, since well just about anything is possible with enough code and we are already controlling outlook from an excel vba script, however, that would be beyond my level of expertise which is pretty much limited to this thread! :biggrin: I would recommend starting a new thread as this thread is quite old and I am the only one who responds anymore. Let me see what I can do though.

I would think this would be easier to accomplish if the entire email was filled out and sent from the code, then we would only need to figure out how to grab the email from the sent folder.

Is the only reason the user sends the email so they can enter email addresses? You could simply have a few cells on the worksheet for entering emails then just pull the value from those cells with your code to fill out the email:

Code:
.Subject = Title    
    .To = ActiveSheet.Range("A1").Value 
    .CC = ActiveSheet.Range("A2").Value 
    .BCC = ActiveSheet.Range("A3").Value

Then use .Send instead of .Display

Then our next piece of code will hopefully grab any email from the sent folder with key words in the subject and print it to pdf and save it. I'll get back to you, but start a new thread and let me know if you get a solution before I do.
 
Last edited:
Upvote 0
I've got a solution that requires a combination of your excel script and a rule in outlook that has a rule set to run a script on emails sent with certain key words. Are you still interested?
 
Upvote 0
Thank you for all your help! I made just a few tweaks, but when I run the code, I get a Run-time error '5' Invalid procedure call or argument.

When I step through, the following repeats over and over again.

If olItem.Class = olMail Then
If olItem.Subject = Esub Then '<-- check for match
SaveAsPDF olItem '< - Call SaveAsPDF code
End If
End If
Next

Can you help?



Rich (BB code):
Sub AttachActiveSheetPDF()
  Dim IsCreated As Boolean
  Dim PdfFile As String, Title As String, Esub As String
  Dim OutlApp As Object
  Dim sendTime As String

    sendTime = Now()
    sendTime = Format(sendTime, "yyyymmm-dd, hh:mm:ss")

  ' ### Define email subject and PDF path & filename ###
  Title = Range("C218").Value
  Esub = "Completed Case Review " & sendTime
  PdfFile = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\" & Title & ".pdf"


  ' ### Export ActiveSheet to PDF ###
  With ActiveSheet
    .ExportAsFixedFormat Type:=xlTypePDF, FileName:=PdfFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
  End With

  ' ### Open Outlook ###
  On Error Resume Next
  Set OutlApp = GetObject(, "Outlook.Application")  '<-- If open, use it
  If Err Then
    Set OutlApp = CreateObject("Outlook.Application")  '<-- If not, open it
    IsCreated = True
  End If
  OutlApp.Visible = True
  On Error GoTo 0

  ' ### Prepare email and attach pdf created above ###
  With OutlApp.CreateItem(0)

    .Subject = Esub
    .To = ""   ' <-- Put email of the recipient here
    .CC = ""
    .Body = "Hello," & vbLf & vbLf _
          & "Please find attached a completed case review." & vbLf & vbLf _
          & "Thank you," & vbLf _
          & Application.UserName & vbLf & vbLf
    .Attachments.Add PdfFile

    ' Try to send
    Application.Visible = True
    .Display True '<-- True forces code to wait for user to send email. Or just automate what the user is doing and change this to .Send
  End With

  Application.Wait (Now + TimeValue("0:00:05"))  '<-- 5 second delay allows email to finish sending

' ### Search Sent Mail folder for emails with same timestamp in subject ###
    Dim olNameSpace As Outlook.Namespace
    Dim olFolder As Outlook.MAPIFolder
    Dim olItem As Object

    Set olNameSpace = OutlApp.GetNamespace("MAPI")
    Set olFolder = olNameSpace.GetDefaultFolder(olFolderSentMail)
    Set olItem = OutlApp.CreateItem(olMailItem)

    For Each olItem In olFolder.Items
        If olItem.Class = olMail Then
            If olItem.Subject = Esub Then  '<-- check for match
                SaveAsPDF olItem '< - Call SaveAsPDF code
            End If
        End If
    Next

    If IsCreated Then OutlApp.Quit  '<-- Quit Outlook if it was not already open
  Set OutlApp = Nothing  '<-- Release the memory of object variable

   ' ### Delete our temp pdf file if not needed anymore ###
  Kill PdfFile

End Sub


Sub SaveAsPDF(MyMail As MailItem)

' ### Requires reference to Microsoft Scripting Runtime ###
' ### Requires reference to Microsoft Outlook Object Library ###
' ### Requires reference to Microsoft Word Object Library ###
' --- In VBE click TOOLS > REFERENCES and check the boxes for all of the above ---

  Dim fso As FileSystemObject
  Dim emailSubject As String
  Dim saveName As String
  Dim blnOverwrite As Boolean
  Dim bPath As String
  Dim strFolderPath As String
  Dim sendEmailAddr As String
  Dim senderName As String
  Dim looper As Integer
  Dim plooper As Integer
  Dim strID As String
  Dim olNS As Outlook.Namespace
  Dim oMail As Outlook.MailItem

  strID = MyMail.EntryID
  Set App = CreateObject("Outlook.Application")
  Set olNS = App.GetNamespace("MAPI")
  Set oMail = olNS.GetItemFromID(strID)

  ' ### Get username portion of sender's email address ###
  sendEmailAddr = oMail.SenderEmailAddress
  senderName = Left(sendEmailAddr, InStr(sendEmailAddr, "@") - 1)

  ' ### USER OPTIONS ###
  blnOverwrite = False ' False = don't overwrite, True = do overwrite

  ' ### Path to directory for saving pdf copy of sent email ###
  bPath = "Z:\Emails - East\2016\"

  ' ### Create Directory if it doesnt exist ###
  If Dir(bPath, vbDirectory) = vbNullString Then
      MkDir bPath
  End If

  ' ### Get Email subject & set name to be saved as ###
  emailSubject = CleanFileName(oMail.Subject)
  saveName = emailSubject & ".mht"
  Set fso = CreateObject("Scripting.FileSystemObject")

  ' ### Save .mht file to create pdf from within Word ###
  oMail.SaveAs bPath & saveName, olMHTML
  pdfSave = bPath & emailSubject & "_" & senderName & "_" & ".pdf"

  ' ### Open Word to convert .mht file to PDF ###
  Dim wrdApp As Word.Application
  Dim wrdDoc As Word.Document
  Set wrdApp = CreateObject("Word.Application")

  ' ### Open .mht file we just saved and export as PDF ###
  Set wrdDoc = wrdApp.Documents.Open(FileName:=bPath & saveName, Visible:=True)
        wrdApp.ActiveDocument.ExportAsFixedFormat OutputFileName:= _
                pdfSave, ExportFormat:= _
                wdExportFormatPDF, OpenAfterExport:=False, OptimizeFor:= _
                wdExportOptimizeForPrint, Range:=wdExportAllDocument, From:=0, To:=0, _
                Item:=wdExportDocumentContent, IncludeDocProps:=True, KeepIRM:=True, _
                CreateBookmarks:=wdExportCreateNoBookmarks, DocStructureTags:=True, _
                BitmapMissingFonts:=True, UseISO19005_1:=False

  wrdDoc.Close
  wrdApp.Quit

  ' ### Delete our temp .mht file ###
  Kill bPath & saveName

  ' ### Uncomment this section to save attachments also ###
  'If oMail.Attachments.Count > 0 Then
  '    For Each atmt In oMail.Attachments
  '        atmtName = CleanFileName(atmt.FileName)
  '        atmtSave = bPath & Format(oMail.ReceivedTime, "yyyy-mm-dd-hhmm") & "_" & atmtName
  '        atmt.SaveAsFile atmtSave
  '    Next
  'End If

  Set oMail = Nothing
  Set olNS = Nothing
  Set fso = Nothing
End Sub


Function CleanFileName(strText As String) As String
Dim strStripChars As String
Dim intLen As Integer
Dim i As Integer
strStripChars = "/\[]:=," & Chr(34)
intLen = Len(strStripChars)
strText = Trim(strText)
For i = 1 To intLen
strText = Replace(strText, Mid(strStripChars, i, 1), "")
Next
CleanFileName = strText
End Function
 
Upvote 0
You modified the filename when you changed the format of sendTime. Colon's are not a valid character in a filename.
Also, make sure you have checked the references required:
Code:
' ### Requires reference to Microsoft Scripting Runtime ###
' ### Requires reference to Microsoft Outlook Object Library ###
' ### Requires reference to Microsoft Word Object Library ###
 
Last edited:
Upvote 0
Thank you.

I removed the colons. I have the required references checked.

When I run the code, i still get a [/COLOR]Run-time error '5' Invalid procedure call or argument.

When I step through, the following repeats over and over again. Could it be a naming issue?

If olItem.Class = olMail Then
If olItem.Subject = Esub Then '<-- check for match
SaveAsPDF olItem '< - Call SaveAsPDF code
End If
End If
Next


Here's the code for everything:

Rich (BB code):
Sub AttachActiveSheetPDF()
  Dim IsCreated As Boolean
  Dim PdfFile As String, Title As String, Esub As String
  Dim OutlApp As Object
  Dim sendTime As String

    sendTime = Now()
    sendTime = Format(sendTime, "yyyymmm-dd, hhmm AM/PM")

  ' ### Define email subject and PDF path & filename ###
  Title = Range("C218").Value
  Esub = "Completed Case Review " & sendTime
  PdfFile = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\" & Title & ".pdf"


  ' ### Export ActiveSheet to PDF ###
  With ActiveSheet
    .ExportAsFixedFormat Type:=xlTypePDF, FileName:=PdfFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
  End With

  ' ### Open Outlook ###
  On Error Resume Next
  Set OutlApp = GetObject(, "Outlook.Application")  '<-- If open, use it
  If Err Then
    Set OutlApp = CreateObject("Outlook.Application")  '<-- If not, open it
    IsCreated = True
  End If
  OutlApp.Visible = True
  On Error GoTo 0

  ' ### Prepare email and attach pdf created above ###
  With OutlApp.CreateItem(0)

    .Subject = Esub
    .To = ""   ' <-- Put email of the recipient here
    .CC = ""
    .Body = "Hello," & vbLf & vbLf _
          & "Please find attached a completed case review." & vbLf & vbLf _
          & "Thank you," & vbLf _
          & Application.UserName & vbLf & vbLf
    .Attachments.Add PdfFile

    ' Try to send
    Application.Visible = True
    .Display True '<-- True forces code to wait for user to send email. Or just automate what the user is doing and change this to .Send
  End With

  Application.Wait (Now + TimeValue("0:00:05"))  '<-- 5 second delay allows email to finish sending

' ### Search Sent Mail folder for emails with same timestamp in subject ###
    Dim olNameSpace As Outlook.Namespace
    Dim olFolder As Outlook.MAPIFolder
    Dim olItem As Object

    Set olNameSpace = OutlApp.GetNamespace("MAPI")
    Set olFolder = olNameSpace.GetDefaultFolder(olFolderSentMail)
    Set olItem = OutlApp.CreateItem(olMailItem)

    For Each olItem In olFolder.Items
        If olItem.Class = olMail Then
            If olItem.Subject = Esub Then  '<-- check for match
                SaveAsPDF olItem '< - Call SaveAsPDF code
            End If
        End If
    Next
    If IsCreated Then OutlApp.Quit  '<-- Quit Outlook if it was not already open
  Set OutlApp = Nothing  '<-- Release the memory of object variable

   ' ### Delete our temp pdf file if not needed anymore ###
  Kill PdfFile

End Sub


Sub SaveAsPDF(MyMail As MailItem)

' ### Requires reference to Microsoft Scripting Runtime ###
' ### Requires reference to Microsoft Outlook Object Library ###
' ### Requires reference to Microsoft Word Object Library ###
' --- In VBE click TOOLS > REFERENCES and check the boxes for all of the above ---

  Dim fso As FileSystemObject
  Dim emailSubject As String
  Dim saveName As String
  Dim blnOverwrite As Boolean
  Dim bPath As String
  Dim strFolderPath As String
  Dim sendEmailAddr As String
  Dim senderName As String
  Dim looper As Integer
  Dim plooper As Integer
  Dim strID As String
  Dim olNS As Outlook.Namespace
  Dim oMail As Outlook.MailItem

  strID = MyMail.EntryID
  Set App = CreateObject("Outlook.Application")
  Set olNS = App.GetNamespace("MAPI")
  Set oMail = olNS.GetItemFromID(strID)

  ' ### Get username portion of sender's email address ###
  sendEmailAddr = oMail.SenderEmailAddress
  senderName = Left(sendEmailAddr, InStr(sendEmailAddr, "@") - 1)

  ' ### USER OPTIONS ###
  blnOverwrite = False ' False = don't overwrite, True = do overwrite

  ' ### Path to directory for saving pdf copy of sent email ###
  bPath = "Z:\Emails - East\2016\"

  ' ### Create Directory if it doesnt exist ###
  If Dir(bPath, vbDirectory) = vbNullString Then
      MkDir bPath
  End If

  ' ### Get Email subject & set name to be saved as ###
  emailSubject = CleanFileName(oMail.Subject)
  saveName = emailSubject & ".mht"
  Set fso = CreateObject("Scripting.FileSystemObject")

  ' ### Save .mht file to create pdf from within Word ###
  oMail.SaveAs bPath & saveName, olMHTML
  pdfSave = bPath & emailSubject & "_" & senderName & "_" & ".pdf"

  ' ### Open Word to convert .mht file to PDF ###
  Dim wrdApp As Word.Application
  Dim wrdDoc As Word.Document
  Set wrdApp = CreateObject("Word.Application")

  ' ### Open .mht file we just saved and export as PDF ###
  Set wrdDoc = wrdApp.Documents.Open(FileName:=bPath & saveName, Visible:=True)
        wrdApp.ActiveDocument.ExportAsFixedFormat OutputFileName:= _
                pdfSave, ExportFormat:= _
                wdExportFormatPDF, OpenAfterExport:=False, OptimizeFor:= _
                wdExportOptimizeForPrint, Range:=wdExportAllDocument, From:=0, To:=0, _
                Item:=wdExportDocumentContent, IncludeDocProps:=True, KeepIRM:=True, _
                CreateBookmarks:=wdExportCreateNoBookmarks, DocStructureTags:=True, _
                BitmapMissingFonts:=True, UseISO19005_1:=False

  wrdDoc.Close
  wrdApp.Quit

  ' ### Delete our temp .mht file ###
  Kill bPath & saveName

  ' ### Uncomment this section to save attachments also ###
  'If oMail.Attachments.Count > 0 Then
  '    For Each atmt In oMail.Attachments
  '        atmtName = CleanFileName(atmt.FileName)
  '        atmtSave = bPath & Format(oMail.ReceivedTime, "yyyy-mm-dd-hhmm") & "_" & atmtName
  '        atmt.SaveAsFile atmtSave
  '    Next
  'End If

  Set oMail = Nothing
  Set olNS = Nothing
  Set fso = Nothing
End Sub


Function CleanFileName(strText As String) As String
Dim strStripChars As String
Dim intLen As Integer
Dim i As Integer
strStripChars = "/\[]:=," & Chr(34)
intLen = Len(strStripChars)
strText = Trim(strText)
For i = 1 To intLen
strText = Replace(strText, Mid(strStripChars, i, 1), "")
Next
CleanFileName = strText
End Function

Thanks again for all your help!
 
Upvote 0
Dude, you're kiddin me, right?:confused:

You got rid of the colons but added a Slash! That also is not an acceptable character in a filename.:hammer:


Go to any file on your computer and rename it. If you try to put a slash or colon, you will get a popup warning that lists all of the characters that are not acceptable. Check it out.
 
Upvote 0
Got it. I don't know what I'm doing!:confused:

I removed the slash. I have the required references checked.

When I run the code, i still get: Run-time error '5': Invalid procedure call or argument.

When I step through, the following repeats over and over again.

If olItem.Class = olMail Then
If olItem.Subject = Esub Then '<-- check for match
SaveAsPDF olItem '< - Call SaveAsPDF code
End If
End If
Next

Here's the code for everything:

Code:
Sub AttachActiveSheetPDF()
  Dim IsCreated As Boolean
  Dim PdfFile As String, Title As String, Esub As String
  Dim OutlApp As Object
  Dim sendTime As String

    sendTime = Now()
    sendTime = Format(sendTime, "yyyymmm-dd, hhmm")

  ' ### Define email subject and PDF path & filename ###
  Title = Range("C218").Value
  Esub = "Completed Case Review " & sendTime
  PdfFile = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\" & Title & ".pdf"


  ' ### Export ActiveSheet to PDF ###
  With ActiveSheet
    .ExportAsFixedFormat Type:=xlTypePDF, FileName:=PdfFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
  End With

  ' ### Open Outlook ###
  On Error Resume Next
  Set OutlApp = GetObject(, "Outlook.Application")  '<-- If open, use it
  If Err Then
    Set OutlApp = CreateObject("Outlook.Application")  '<-- If not, open it
    IsCreated = True
  End If
  OutlApp.Visible = True
  On Error GoTo 0

  ' ### Prepare email and attach pdf created above ###
  With OutlApp.CreateItem(0)

    .Subject = Esub
    .To = ""   ' <-- Put email of the recipient here
    .CC = ""
    .Body = "Hello," & vbLf & vbLf _
          & "Please find attached a completed case review." & vbLf & vbLf _
          & "Thank you," & vbLf _
          & Application.UserName & vbLf & vbLf
    .Attachments.Add PdfFile

    ' Try to send
    Application.Visible = True
    .Display True '<-- True forces code to wait for user to send email. Or just automate what the user is doing and change this to .Send
  End With

  Application.Wait (Now + TimeValue("0:00:05"))  '<-- 5 second delay allows email to finish sending

' ### Search Sent Mail folder for emails with same timestamp in subject ###
    Dim olNameSpace As Outlook.Namespace
    Dim olFolder As Outlook.MAPIFolder
    Dim olItem As Object

    Set olNameSpace = OutlApp.GetNamespace("MAPI")
    Set olFolder = olNameSpace.GetDefaultFolder(olFolderSentMail)
    Set olItem = OutlApp.CreateItem(olMailItem)

    For Each olItem In olFolder.Items
        If olItem.Class = olMail Then
            If olItem.Subject = Esub Then  '<-- check for match
                SaveAsPDF olItem '< - Call SaveAsPDF code
            End If
        End If
    Next

    If IsCreated Then OutlApp.Quit  '<-- Quit Outlook if it was not already open
  Set OutlApp = Nothing  '<-- Release the memory of object variable

   ' ### Delete our temp pdf file if not needed anymore ###
  Kill PdfFile

End Sub


Sub SaveAsPDF(MyMail As MailItem)

' ### Requires reference to Microsoft Scripting Runtime ###
' ### Requires reference to Microsoft Outlook Object Library ###
' ### Requires reference to Microsoft Word Object Library ###
' --- In VBE click TOOLS > REFERENCES and check the boxes for all of the above ---

  Dim fso As FileSystemObject
  Dim emailSubject As String
  Dim saveName As String
  Dim blnOverwrite As Boolean
  Dim bPath As String
  Dim strFolderPath As String
  Dim sendEmailAddr As String
  Dim senderName As String
  Dim looper As Integer
  Dim plooper As Integer
  Dim strID As String
  Dim olNS As Outlook.Namespace
  Dim oMail As Outlook.MailItem

  strID = MyMail.EntryID
  Set App = CreateObject("Outlook.Application")
  Set olNS = App.GetNamespace("MAPI")
  Set oMail = olNS.GetItemFromID(strID)

  ' ### Get username portion of sender's email address ###
  sendEmailAddr = oMail.SenderEmailAddress
  senderName = Left(sendEmailAddr, InStr(sendEmailAddr, "@") - 1)

  ' ### USER OPTIONS ###
  blnOverwrite = False ' False = don't overwrite, True = do overwrite

  ' ### Path to directory for saving pdf copy of sent email ###
  bPath = "Z:\Emails - East\2016\"

  ' ### Create Directory if it doesnt exist ###
  If Dir(bPath, vbDirectory) = vbNullString Then
      MkDir bPath
  End If

  ' ### Get Email subject & set name to be saved as ###
  emailSubject = CleanFileName(oMail.Subject)
  saveName = emailSubject & ".mht"
  Set fso = CreateObject("Scripting.FileSystemObject")

  ' ### Save .mht file to create pdf from within Word ###
  oMail.SaveAs bPath & saveName, olMHTML
  pdfSave = bPath & emailSubject & "_" & senderName & "_" & ".pdf"

  ' ### Open Word to convert .mht file to PDF ###
  Dim wrdApp As Word.Application
  Dim wrdDoc As Word.Document
  Set wrdApp = CreateObject("Word.Application")

  ' ### Open .mht file we just saved and export as PDF ###
  Set wrdDoc = wrdApp.Documents.Open(FileName:=bPath & saveName, Visible:=True)
        wrdApp.ActiveDocument.ExportAsFixedFormat OutputFileName:= _
                pdfSave, ExportFormat:= _
                wdExportFormatPDF, OpenAfterExport:=False, OptimizeFor:= _
                wdExportOptimizeForPrint, Range:=wdExportAllDocument, From:=0, To:=0, _
                Item:=wdExportDocumentContent, IncludeDocProps:=True, KeepIRM:=True, _
                CreateBookmarks:=wdExportCreateNoBookmarks, DocStructureTags:=True, _
                BitmapMissingFonts:=True, UseISO19005_1:=False

  wrdDoc.Close
  wrdApp.Quit

  ' ### Delete our temp .mht file ###
  Kill bPath & saveName

  ' ### Uncomment this section to save attachments also ###
  'If oMail.Attachments.Count > 0 Then
  '    For Each atmt In oMail.Attachments
  '        atmtName = CleanFileName(atmt.FileName)
  '        atmtSave = bPath & Format(oMail.ReceivedTime, "yyyy-mm-dd-hhmm") & "_" & atmtName
  '        atmt.SaveAsFile atmtSave
  '    Next
  'End If

  Set oMail = Nothing
  Set olNS = Nothing
  Set fso = Nothing
End Sub


Function CleanFileName(strText As String) As String
Dim strStripChars As String
Dim intLen As Integer
Dim i As Integer
strStripChars = "/\[]:=," & Chr(34)
intLen = Len(strStripChars)
strText = Trim(strText)
For i = 1 To intLen
strText = Replace(strText, Mid(strStripChars, i, 1), "")
Next
CleanFileName = strText
End Function
Can you help?
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,637
Latest member
Ezio2866

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