Send Email With Macro

Re: Sending Email With Vba Loop Function

Jason

For a start I don't see any code there that's sending an email.

Secondly you should avoid using SendKeys.

Re: Sending Email With Vba Loop Function

You can use CDO and avoid outlook entirely

Dim iMsg As Object 
    Dim iConf As Object 
    Dim Flds As Variant 
     
    Set iMsg = CreateObject("CDO.Message") 
    Set iConf = CreateObject("CDO.Configuration") 
     
    iConf.Load -1 ' CDO Source Defaults
    Set Flds = iConf.Fields 
    With Flds 
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "mail.yourserverhere.com" 
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25 ' usually 25
        .Update 
    End With 
     
    With iMsg 
        Set .Configuration = iConf 
        .To = "[email protected]" 
        .CC = "" 
        .BCC = "" 
        .From = "[email protected]" 
        .Subject = ""
        .TextBody = ""
        .Send 
    End With 
     
    Set iMsg = Nothing 
    Set iConf = Nothing

Re: Sending Email With Vba Loop Function

You probably need to add a reference to the Microsoft Forms 2.0 Object Library

That single piece of code shouldnt give any errors... what error was it?

Re: Sending Email With Vba Loop Function

Quote from jstevens

That was just the section of the code I was having a problem with...and I'm stuck with using send keys because I am using a work computer and I don't want to get the alert messages when I try to automatically send emails. This is a bit more of the code:

Code

'Make a copy of the active sheet and save it to
     'a temporary file
    ActiveSheet.Copy
    Set WB = ActiveWorkbook
    TempFilePath = Environ$("temp") & "\"
    FileName = "Project Request - " & strName & " " & Format(Now, "mm.dd.yy") & ".xls"
    'FileName = "Temp.xls"
    On Error Resume Next
    Kill TempFilePath & FileName
    'Kill "C:\" & FileName
    
    On Error GoTo 0
    WB.SaveAs TempFilePath & FileName
    'WB.SaveAs FileName:="C:\" & FileName
     
     'Create and show the outlook mail item
    Set oApp = CreateObject("Outlook.Application")
    Set oMail = oApp.CreateItem(0)
    With oMail
         'Uncomment the line below to hard code a recipient
         .To = "[email protected]"
         'Uncomment the line below to hard code a subject
         .Subject = Subj
        .Attachments.Add WB.FullName
        .Display
    End With
 
    'Wait one second before sending keystrokes
    Do Until Application
        Application.Wait (Now + TimeValue("0:00:01"))
        Application.SendKeys "%s"
    Loop

Display More

Wish somebody could fix this code too. tried others and it seems this one is the only one that can work for me as well, as i'm on a company system too and need to send through outlook just as he ran the code above.

works just fine for me too... attatches the xls, just doesnt enter the subject, and stalls before send. after i send it it goes back to excel with a error message. if somebody knows how to make this work would be very cool

Re: Sending Email With Vba Loop Function

Guys... stop with the sendkeys

Use CDO and you have no confirm message to click through.

Re: Sending Email With Vba Loop Function

btw, unless youre setting 'Subj' to a value... you're not setting anything for the subject.

Try: "mysubjecthere" with quotes, dont do it as a variable

Re: Sending Email With Vba Loop Function

Quote

For a start I don't see any code there that's sending an email.

Can't make it too easy for you Norie

Jason, in addition not assuming we have a Crystal ball, don't use Thread Titles to state what you THINK is your answer!

Re: Send Email With Macro

"Sorry, my bad...I think the reason that I can't use CDO is because there are going to be people on many different servers using this. I know send keys isn't the most reliable but I'm stuck at this point. I was just wondering if there is anyway to tell excel that the email was sent and if not keep trying until it is sent."

Well, if theyre all running windows 2000 or later you shouldn't have a problem with it. I do something similar at my work - I have an application where when the user is finished and closes the excel file it sends a log file to me via CDO... this has worked on every computer at my work for 20+ people... and these people all have restricted user access on their PCs as well, and no problems at all. (I have the outlook method as the backup, which the log would make note of when used, but it hasnt had to be used once.)