+ Reply to Thread
Results 1 to 1 of 1

For...Next Loop issue

Hybrid View

  1. #1
    Registered User
    Join Date
    08-20-2010
    Location
    Montreal
    MS-Off Ver
    Excel 2003, 2007
    Posts
    1

    For...Next Loop issue

    I wrote a macro for Excel 2003 and 2007 that send e-mail directly from Excel, extracting the address, file to attach and division from three columns. The macro works superbly well, except for the loop which doesn't work at all.

    Complete code:

    Sub Mail_Discretionary()
        
        Dim OutApp As Object
        Dim OutMail As Object
    
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)
        
        Dim month As Integer
        month = Range("B2").Value
    
        Dim annee As String
        annee = Range("B12").Value
        
        
        Dim Email As String
        Dim Folder As String
        Dim dept As String
        Dim i As Integer
            
    For i = 1 To 150
        Windows("Macro Email Sending.xlsm").Activate
        Range("E4").Select
        dept = ActiveCell.Offset(i, 0).Value
        Folder = ActiveCell.Offset(i, 1).Value
        Email = ActiveCell.Offset(i, 2).Value
        'If dept = "" Then
         '   Stop
        'Else
        'On Error Resume Next
       
        With OutMail
            .To = Email
            .CC = ""
            .BCC = ""
            .Subject = "Forecast "
            .Body = "Please find your report attached."
            .Attachments.Add "C:\Financial Reporting & Budgeting\FY Forecast\CURRENT MONTH\" & Folder & "\Discretionary Report - " & dept & ".xlsm"
            .Display
            
         End With
        'Set OutMail = Nothing
        'Set OutApp = Nothing
        'End If
    Next i
        
    End Sub
    As it is now, it adds all attachments to a single e-mail rather than one e-mail for each rows...
    Last edited by FK7Design; 08-20-2010 at 10:58 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1