Macro using Excel and Outlook/Exchange

[mig1980]

Good day everyone. I am looking to build a Macro based out of Outlook and it needs to do the following:
1) Send out an email with an attachment of a file that will be located in a shared drive. It will be a PDF most of the time but a different name everytime.
2) It must contain a specific subject line and body
3) There are about 200 people this email will go out to and they are in about 50 different districts. We want 50 different emails (with multiple email addresses per email) going out per district. All of the user information is listed in an Excel spreadsheet.

I want to user using this macro to have the least amout of interaction possible. The subject, and body will be static.

I am fairly new to Macros and would really like some help.

Thank you.

[MartinLiss]

Since it looks like you are doing this within Outlook rather than by using VB6 I've moved your thread to here.

[mig1980]

Thank you. Ultimately I would like to automate all of this using a macro button or something of the sort.

[Siddharth Rout]

I am fairly new to Macros and would really like some help.

Then this is the place I would suggest you to start with.... (See under 'Outlook')

http://www.vbforums.com/showpost.php...53&postcount=2

[mig1980]

This was no help. I need help with the code to do this. I am very new to VB code and how to construct something that would work. Any further help would be appreciated.

Then this is the place I would suggest you to start with.... (See under 'Outlook')

http://www.vbforums.com/showpost.php...53&postcount=2

[Siddharth Rout]

This was no help.

Are you sure?

1) Send out an email with an attachment of a file that will be located in a shared drive. It will be a PDF most of the time but a different name everytime.
2) It must contain a specific subject line and body

The answer to the above two questions lies in the 5th link under Outlook in the FAQ.

We will come to the 3rd question after you get the above two.

Let me know if I have misunderstood your query...

[mig1980]

You are correct! The link you pointed me to looks like the correct path for what i want to accomplish. Although I see three different code samples in different languages. It seems like they don't all do the same thing. It seems like the sample in VB 6 would be enough but the attachment wouldn't always be the same name and I would need to pull (#3) my email from the Excel spreadsheet. It should not be one email for all 200 clients. The emails should be split up by Company (which is a column in the Excel spreadsheet) and sent out that way. We would be looking at about 50 emails.

The whole issue here is that soem clients are having these emails flagged by either their junk mail filter or their company firewall/spam filter. At the moment we add all clients to the BCC list and send it out that way.

[Siddharth Rout]

It seems like the sample in VB 6 would be enough but the attachment wouldn't always be the same name

I understand that. That is just an FAQ on how you should be approaching your quest. Now If the files are on shared drive, my next question would be... How do I retrieve them? Do I use a Dialog box? or do I have the name of the files stored in a file?

I can give you the exact code on how to accomplish what you want but then the purpose of this forum would be defeated.

Mig1980, I would suggest that you take the first code (vb6), understand it, amend it to suit your needs, create a sample code and test it. And yes if you get stuck then post the code that you tried and then yes, we will definitely help you This way you will also learn... This is the way most of us learnt...

As far as getting different emails are concerned, that is not important for the moment. We will tackle that later. Use a dummy email if you want to for the time being...

Remember, I will be glad to help you till the time you get what you are looking for... provided you help yourself...

[mig1980]

Thank you very much. Just one question for the time being, if I wanted a a dialog box to retrieve an attachment how would that be done?

[Siddharth Rout]

Thank you very much. Just one question for the time being, if I wanted a a dialog box to retrieve an attachment how would that be done?

Search the forum on Application.GetOpenFilename

[mig1980]

OK so, I have worked with the sample code a bit and I got it to work correctly for the most part. I have been trying to get the Application.GetOpenFilename to prompt me with a browsing window to select the file I need to attach. I have not been successful with this part. I even broke it into a function but I have not been successful. Here is the code:

Option Explicit
'Add a reference to MS Outlook xx.0 Object Library
Private Sub Command1_Click()
    Dim oApp As Outlook.Application
    Dim oEmail As Outlook.MailItem
    Set oApp = New Outlook.Application
    Set oEmail = oApp.CreateItem(olMailItem)
    With oEmail
        .To = "vette_red1998@yahoo.com"
        .CC = "miguelg@sscal.com"
        .Subject = "(SF)2 Weekly Update"
        .BodyFormat = olFormatRichText
        .HTMLBody = "Good afternoon (SF)&#178; Members,<br><br>Please find attached this week's Update.<br><br>Thank you,<br>Miguel"
        .ReadReceiptRequested = True
        Call OpenFile
        .Attachments.Add OpenFile(), olByValue
        .Recipients.ResolveAll
        .Save
        .Display 'Show the email message and allow for editing before sending
        '.Send 'You can automatically send the email without displaying it.
    End With
    Set oEmail = Nothing
End Sub

Private Function OpenFile() As String

Dim NewFN As String

    NewFN = Application.GetOpenFilename(FileFilter:="PDF File (*.pdf), *.pdf", Title:="Please select a file")
If NewFN = False Then
     ' They pressed Cancel
     
     MsgBox "Stopping because you did not select a file"
     
    Exit Function
     
Else
     
   Application.Open FileName:=NewFN
     
End If
OpenFile = NewFN

End Function

[mig1980]

any further help anyone?

[Siddharth Rout]

Will have a look at it tonight

[mig1980]

Thank you. Let me know your thoughts. I will continue work on it today.

[Siddharth Rout]

I haven't tested it... just made few quick adjustments in your code....

See if it works. if it doesn't then tell me the error message that you are getting. I will also test it later in the night...

Option Explicit

Dim AttachFile As String, NewFN As String, boolExit As Boolean

Private Sub Command11_Click()
    Dim oApp As Outlook.Application, oEmail As Outlook.MailItem
    
    Set oApp = New Outlook.Application
    Set oEmail = oApp.CreateItem(olMailItem)
    
    OpenFile
    
    If boolExit = True Then
        msgobx "Please select an attachment"
        Exit Sub
    End If
    
    With oEmail
        .To = "vette_red1998@yahoo.com"
        .CC = "miguelg@sscal.com"
        .Subject = "(SF)2 Weekly Update"
        .BodyFormat = olFormatRichText
        .HTMLBody = "Good afternoon (SF)&#178; Members,<br><br>Please find attached this week's Update.<br><br>Thank you,<br>Miguel"
        .ReadReceiptRequested = True
        
        .Attachments.Add AttachFile, olByValue
        .Recipients.ResolveAll
        .Save
        .Display 'Show the email message and allow for editing before sending
        '.Send 'You can automatically send the email without displaying it.
    End With
    Set oEmail = Nothing
End Sub

Private Sub OpenFile()
    NewFN = Application.GetOpenFilename(FileFilter:="PDF File (*.pdf), *.pdf", Title:="Please select a file")
    On Error Resume Next
    If NewFN = False Then
        'Pressed Cancel
        MsgBox "Stopping because you did not select a file"
        boolExit = True
        Exit Sub
    Else
        AttachFile = NewFN
    End If
    On Error GoTo 0
End Sub

[mig1980]

Thank you for the updated code. I get a similar error to what I got when I ran my code. It stops on the Application.GetOpenFilename section with an error stating: "Run-time error '438': Object doesn't support this property or method"

[Siddharth Rout]

Ah I got it...

Outlook doesn't support .GetOpenFilename. Only Excel, Word and PPoint does it... Let me give you an alternative.

Ok Click on Menu Tools~~> References and select the "Microsoft Common Dialog Control 6.0". If you cannot see it then click on "Browse" and go to the windows system directory and select the comdlg32.ocx file.

Now in the toolbox, you should see the control. if you don't then right clcik on the toolbox and select the "Additional Controls" option. Then scroll down and select the "Microsoft Common Dialog Control 6.0" Simply add it to your form.

Use this code in lieu of OpenFile()

Private Sub OpenFile()
    CommonDialog1.DialogTitle = "Select the File..."
    CommonDialog1.Flags = cdlOFNFileMustExist
    CommonDialog1.Filter = "PDF File  (*.pdf)*.pdf"
    CommonDialog1.ShowOpen

    If Len(CommonDialog1.FileName) <> 0 Then
        AttachFile = CommonDialog1.FileName
    Else
        MsgBox "Stopping because you did not select a file"
        boolExit = True
    End If
End Sub

[mig1980]

That makes sense. Thank you.

[mig1980]

I am getting an invalid qualifier compiling error from the CommonDialog1. I checked the Refrences and I can see the Microsoft Common Dialog Control 6.0 (SP3) checked and it is in the third place from top to bottom of checked items.

[mig1980]

any more help here?

[Siddharth Rout]

I just tried it and it works...

Can you show me the complete code that you are trying and also highlight the line where you are getting the error...

[mig1980]

Hereb is the code, and the bold area is what gets highlisted saying "Compile Error: Variable not Defined":

Option Explicit

Dim AttachFile As String, boolExit As Boolean

Private Sub Command11_Click()
    Dim oApp As Outlook.Application, oEmail As Outlook.MailItem
    
    Set oApp = New Outlook.Application
    Set oEmail = oApp.CreateItem(olMailItem)
    
    OpenFile
    
    If boolExit = True Then
        MsgBox "Please select an attachment"
        Exit Sub
    End If
    
    With oEmail
        .To = "vette_red1998@yahoo.com"
        .CC = "miguelg@sscal.com"
        .Subject = "(SF)2 Weekly Update"
        .BodyFormat = olFormatRichText
        .HTMLBody = "Good afternoon (SF)&#178; Members,<br><br>Please find attached this week's Update.<br><br>Thank you,<br>Miguel"
        .ReadReceiptRequested = True
        
        .Attachments.Add AttachFile, olByValue
        .Recipients.ResolveAll
        .Save
        .Display 'Show the email message and allow for editing before sending
        '.Send 'You can automatically send the email without displaying it.
    End With
    Set oEmail = Nothing
End Sub

Private Sub OpenFile()
    CommonDialog1.DialogTitle = "Select the File..."
    CommonDialog1.Flags = cdlOFNFileMustExist
    CommonDialog1.Filter = "PDF File  (*.pdf)*.pdf"
    CommonDialog1.ShowOpen

    If Len(CommonDialog1.FileName) <> 0 Then
        AttachFile = CommonDialog1.FileName
    Else
        MsgBox "Stopping because you did not select a file"
        boolExit = True
    End If
End Sub

[Siddharth Rout]

Did you add the commondialog control on the userform? And If yes, then did you rename it to something else?

[mig1980]

Not sure I understand where the userform is. I am very new to VB coding. I have all coding added to the section marked "ThisOutlookSession" and the project is called VbaProject.OTM. The code I pasted is all I have.

[Siddharth Rout]

ok, let me attach a snapshot. give me 15 mins

[mig1980]

Thank you. I truly appreciate the help.

[Siddharth Rout]

Ok, Let's go to basics now

Add a userform as shown in the picture. Then Add 1 Textbox, 3 Commandbuttons and 1 CommonDialog control.

Once done, let me know. We will take it from there...

[mig1980]

I get a prompt saying "Invalid Argument" when I try to add the Common Dialog box.

[Siddharth Rout]

Show me a screenshot...

[mig1980]

I just registered the .ocx file and now it says "The control could not be created because it is not properly licensed. Just and FYI, I am running Windows Vista 64bit and Office 2003 SP3. Here is a screen shot

[Siddharth Rout]

I am running Windows Vista 64bit and Office 2003 SP3.

Ah!!!

What edition of MS Office are you using? I am using the Professional edition.

[mig1980]

I am also using Office Professional 2003.

[Siddharth Rout]

Ok, leave this with me. It's already 1:55 AM in the morning and I am hitting the sack. i will have a look at it later in the day today....

Don't worry, we will get this sorted

[mig1980]

Thank you very much and have a good night.

[Siddharth Rout]

Ok Try this...

If you are the administrator of that machine then open an elevated Command Prompt by clicking Start, click All Programs, click Accessories, right-click Command Prompt, and then click Run as administrator.

Type the following command

regsvr32  %Systemroot%\System32\comdlg32.ocx

If the dll is successfully registered then you will get the following message...

DllRegisterServer in C:\WINDOWS\System32\comdlg32.ocx succeeded.

Let me know if you see this message or not... we will take it from there....

[mig1980]

I had already tried registering the ocx file and I did it again. Here is what I saw. By the way I continue to get the error stating that the control could not be created because it is not properly licensed when I try to add the "CommonDialog" control to the UserForm.

[Siddharth Rout]

Ok... Forget the commondialog.... Leave everything.... We are going to use a wicked way now....

Set a reference to Microsoft Excel Object Library from the Tools~~> Reference menu

and then use this code...

Option Explicit

Dim AttachFile As String, NewFN As String, boolExit As Boolean

Private Sub Command11_Click()
    Dim oApp As Outlook.Application, oEmail As Outlook.MailItem
    
    Set oApp = New Outlook.Application
    Set oEmail = oApp.CreateItem(olMailItem)
    
    OpenFile
    
    If boolExit = True Then
        msgobx "Please select an attachment"
        Exit Sub
    End If
    
    With oEmail
        .To = "vette_red1998@yahoo.com"
        .CC = "miguelg@sscal.com"
        .Subject = "(SF)2 Weekly Update"
        .BodyFormat = olFormatRichText
        .HTMLBody = "Good afternoon (SF)² Members,<br><br>Please find attached this week's Update.<br><br>Thank you,<br>Miguel"
        .ReadReceiptRequested = True
        
        .Attachments.Add AttachFile, olByValue
        .Recipients.ResolveAll
        .Save
        .Display 'Show the email message and allow for editing before sending
        '.Send 'You can automatically send the email without displaying it.
    End With
    Set oEmail = Nothing
End Sub


Private Sub OpenFile()
    Dim xlApp As New Excel.Application
    Dim Filterr As String, Caption As String, SelectedFile As String
    
    xlApp.Visible = False
    Filterr = "PDF files (*.pdf),*.pdf"
    Caption = "Please Select a File "
    SelectedFile = xlApp.GetOpenFilename(Filterr, , Caption)
    xlApp.Quit
    Set xlApp = Nothing

    If Len(SelectedFile) <> 0 Then
        AttachFile = SelectedFile
    ElseIf SelectedFile = False Then
        MsgBox "Stopping because you did not select a file"
        boolExit = True
    End If
End Sub

[mig1980]

I receive the following error and I don't see the variable or a few others defined anywhere.

[Siddharth Rout]

Add this line

Dim Filterr as String, Caption as String

after

Dim xlApp As New Excel.Application

[mig1980]

OK, a few more that are not defined: TheUser, and SelectedFile. Are they both String?