Excel form received but macro not forwarding file on

[SA1983]

Hi all,
New to macros and thought i would try and create one for a new form.
Basically, individual1 completes the form and uses the macro to submit to individual2 who then approves/rejects and uses another macro to send to individual3.

The first bit works fine but when the second user receives the file and uses the macro button it comes up with: Runtime error 1004 - method 'sendmail' of object '_workbook' failed.
All macros worked when i set up the file so I wonder if it is because the second user will open from their inbox rather than a saved file?

Macro used is below, any help/advice would be appreciated. Thanks


Sub Button2_Click()
ActiveWorkbook.SendMail Recipients:=Range("b45:B46").Value, Subject:="Expense form"
End Sub
Sub Macro2()
ActiveWorkbook.SendMail Recipients:=Range("C45:C48").Value, Subject:="Expense approved"
End Sub
Sub Macro3()
ActiveWorkbook.SendMail Recipients:=Range("D45").Value, Subject:="Expense rejected"
End Sub

 

[Rosen]

Hello SA1983,

Please note that the SendMail method sends an email via the installed mail system, if on the server it is located when accessed through email does not have an installed mail system, that would explain while it is failing, or if that user doesn't have access to the that mail system, either way. If they download the file locally before executing the Macro2 or Macro3 methods, this (in theory) should resolve your issue.

 

[SA1983]

Hi, thanks for your reply.
I don't really understand the mail systems so will look into that.
Saving the file to the desktop etc and then using the macro does work but it creates a bit of extra work saving, sending and then deleting files that i was hoping to avoid.
Thanks

Hello SA1983,

Please note that the SendMail method sends an email via the installed mail system, if on the server it is located when accessed through email does not have an installed mail system, that would explain while it is failing, or if that user doesn't have access to the that mail system, either way. If they download the file locally before executing the Macro2 or Macro3 methods, this (in theory) should resolve your issue.

 

[Rosen]

Try adding the line

ActiveWorkbook.SaveAs VBA.Interaction.Environ("TEMP") & "\" & ActiveWorkbook.Name

to the beginning of Macro2 and Macro3 to force the save, so the user isn't the one doing the work at least.

Or something like this.

Hope this helps.

 

[SA1983]

Hi Rosen, thanks for this, it solved the issue for me.
I did come across another issues (once saved, future files requested whether i wanted to overwrite), but i managed to find a macro which automatically saves so no the single macro saves and sends the file as intended.

Thanks for your help!

Try adding the line

ActiveWorkbook.SaveAs VBA.Interaction.Environ("TEMP") & "\" & ActiveWorkbook.Name

to the beginning of Macro2 and Macro3 to force the save, so the user isn't the one doing the work at least.

Or something like this.

Hope this helps.

 

[SA1983]

Not sure what happened with my wording there but meant to say I found a line to add to the macro that auto saves and sends the file as intended!