#Vba create outlook email code#

'Loop Through each External Link in ActiveWorkbook and Break itĭestinWB.BreakLink Name:=ExternalLinks(x), Type:=xlLinkTypeExcelLinks Set DestinWB = Workbooks.Open(TempFilePath & TempFileName & FileExtStr)ĮxternalLinks = DestinWB.LinkSources(Type:=xlLinkTypeExcelLinks) SourceWB.SaveCopyAs TempFilePath & TempFileName & FileExtStr If TempFileName = False Then Exit Sub 'Handle if user cancelsįileExtStr = '.' & LCase(Right(SourceWB.Name, Len(SourceWB.Name) - InStrRev(SourceWB.Name, '.',, 1))) 'File Name', Type:=2, Default:=DefaultName) TempFileName = Application.InputBox('What would you like to name your attachment? (No Special Characters!)', _ 'Determine Default File Name for InputBoxĭefaultName = Left(SourceWB.Name, InStrRev(SourceWB.Name, '.') - 1) If UserAnswer = vbNo Then Exit Sub 'Handle if user cancels

#Vba create outlook email code#

'Do you wish to proceed?', vbYesNo, 'VBA Code Found!') 'If you proceed the VBA code will not be included in your email attachment.

UserAnswer = MsgBox('There was VBA code found in this xlsx file. If SourceWB.FileFormat = 51 And SourceWB.HasVBProject = True Then 'PURPOSE: Create email message with ActiveWorkbook attached Now, this code was intended to suit my needs, but hopefully I've added enough comments to allow you to easily tweak it to fit your specific needs. So I would like to share with you what I use to email my Excel workbooks and worksheets as attachments. Having to type this out every time can be a bit annoying. I also like to add some sort of message in the email such as ' Please see attached' or ' Attached is your requested data'.

I can't tell you how many times I've attached a file named 'Book1.xlsx' to someone. This annoyance inspired me to create a couple of VBA macros allowing me to add a little more functionality to Microsoft's Email As Attachment idea.įor one, I hate not being able to name the attached file. The only thing I dislike about this built-in feature is the lack of customization as I find myself repeatedly making the same changes over and over again. It's great for quickly sending one-off data requests to someone throughout the day. I absolutely love using the Email As Attachment functionality provided by Excel.