Saltar al contenido
PROGRAMAR EN VBA MACROS DE EXCEL

Envio mail con libro adjunto desde Excel


(adsbygoogle = window.adsbygoogle || []).push({});

Antes de seguir recomiendo leer un excelente libro sobre Excel que te ayudará operar las planillas u hojas de cálculo, haz click acá, si quieres aprender sobre Excel, en inglés, entonces debes hacer click here. Si lo que necesitas es aprender o profundizar sobre la programación de macros con VBA este es unos de los mejores cursos on line que he visto en internet.

  

Como enviar mail con Excel adjuntando todo el libro actual, fue algo que me solicitaron responder en un foro donde participo, si bien en la gran cantidad de post publicados hay varios ejemplos que tratan el tema de envío de mail con Excel, como por ejemplo  como enviar un mail con Excel, como crear alertas y enviar mail; macro emite aviso y envía mail a varios destinatarios; estas macros tienen una particularidad y es que todas estas macros hacían referencia a configurar directamente el servidor SMTP de Gmail, Yahoo o Hotmail; en este post voy a mostrar un ejemplo sobre como enviar mail con Excel mediante Outlook adjuntando el archivo actual.

Este ejemplo es un poco más complejo que las anteriores macros que permiten mandar mail desde Outlook, primero lo que se hace es determinar la ruta y nombre completo del archivo actual; luego se procede a determinar el nombre solamente del archivo a los fines de incorporarlo en una variable y poder agregarle otra extensión de archivo de Excel, en este caso «xlsx»; determinado el nombre se guarda  una copia del libro actual, utilizando el nombre, pero con extensión «xlsx».

Una vez realizado los procedimientos descritos anteriormente se procede  a determinar el destinatario, sujeto, cuerpo del mensaje, como así también determinamos el archivo adjunto, indicándole la ruta previamente determinada, enviado el mail sale un msgbox que muestra que el mail se mandó con éxito, caso contrario sale un mensaje con el error.

Por último se procede a eliminar el archivo que fue creado a los fines de poder ser enviado como adjunto por mail, el comando que elimina el archivo es Kill, en este caso se usa Kill (mydoc), donde «mydoc» es la variable que contiene el path o dirección del archivo creado.

Existen otras publicaciones recientes que se relacionan con el envío de mails utilizando Outlook y que fueron denominados, envio de mail utilizando Outlook y adjuntando una sola hojaenvío de mail mediante Outlook, como enviar mail desde Excel con Outlook agregando un link, hyperlink  o hipertexto.

El ejemplo contenido en esta presentación es posible descargarlo desde el link que se encuentra al final del post, solicito que colabore a seguir manteniendo está página y poder seguir agregando macros que automatizan Excel, como así también invito a compartirlo en las redes sociales.

El vídeo que sigue muestra una explicación más detallada y gráfica de la macro presentada, recomiendo observar para una más fácil comprensión de la macro; suscribe a nuestra web desde la parte superior derecha de la página ingresando tu mail y a nuestro canal de You Tube para recibir en tu correo vídeos explicativos sobre macros interesantes, como  por ejemplo como copiar varias tablas vinculadas de Excel a Wordcomo ejecutar una macro dependiendo del valor de una celdapasar datos de listbox a hoja de Excelbuscar en listbox mientras escribes en textboxcrear indice a hojas del mismo libro y al menú principalconectar Excel con Access e insertar datos y muchos ejemplos más.

function onYtEvent(payload) {
if (payload.eventType == ‘subscribe’) {
// Add code to handle subscribe event.
} else if (payload.eventType == ‘unsubscribe’) {
// Add code to handle unsubscribe event.
}
if (window.console) { // for debugging only
window.console.log(‘YT event: ‘, payload);
}
}

Código que se inserta en un userform

Sub SendMailbyOutlookCFile()
On Error Resume Next
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim OA, OM As Object
Dim Path, TD, fn, mydoc As String
TD = Format(Date, «ddmmyyyy»)
Path = ThisWorkbook.Path & «»
fn = ActiveWorkbook.Name
FullName = Split(fn, Application.PathSeparator)
a = FullName(UBound(FullName))
pto = InStr(a, «.»)
fn = Left(a, pto – 1)
mydoc = Path & fn & «.xlsx»
ActiveWorkbook.SaveCopyAs Filename:=mydoc
Set OA = CreateObject(«Outlook.Application»)
Set OM = OA.CREATEITEM(0)
dest = Cells(3, «E»)
With OM
    .To = dest ‘Cargar el mail de destino, ejemplo «xxxx@gmail.com» (incluida las comas)
    .CC = «»
    .BCC = «»
    .Subject = «Reporte Mensual»
    .Body = «Estimado, en el archivo adjunto se encuentra el reporte mensual » & » al » & TD & » confirmar recepción»
    .attachments.Add mydoc
    .Send
End With
If Err.Number = 0 Then
       SendMail_Gmail = True
       MsgBox «El mail con archivo adjunto fue enviado con éxito», vbInformation, «AVISO»
Else
       MsgBox «Se produjo el siguiente error: » & Err.Description, vbCritical, «Error nro » & Err.Number
End If
Kill (mydoc)
Set OM = Nothing
Set OA = Nothing
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

Si te fue de utilidad puedes INVITARME UN CAFÉ y de esta manera ayudar a seguir manteniendo la página, CLICK para descargar en ejemplo en forma gratuita.

(adsbygoogle = window.adsbygoogle || []).push({});

If this post was helpful INVITE ME A COFFEE and so help keep up the page, CLICK to download free example.

Si te gustó por favor compártelo con tus amigos
If you liked please share it with your friends      

Entradas relacionadas

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

Comentarios (38)

Excellent site you’ve got here.. It’s hard to find quality
writing like yours these days. I seriously appreciate
people like you! Take care!!

Responder

Hi there just wanted to give you a brief heads up and let you
know a few of the pictures aren’t loading correctly.
I’m not sure why but I think its a linking issue.
I’ve tried it in two different web browsers and both show the same outcome.

Responder

Fastidious respond in return of this issue with genuine arguments and explaining the whole
thing regarding that.

Responder

Excellent beat ! I would like to apprentice while you amend your site,
how could i subscribe for a blog website?

The account aided me a acceptable deal. I had been a little bit acquainted of this your broadcast offered bright clear concept

Responder

Excellent post. I absolutely appreciate this site. Keep writing!

Responder

If some one wishes to be updated with newest technologies then he must be visit this web page and be up to date
daily.

Responder

I am regular visitor, how are you everybody?
This piece of writing posted at this site is really pleasant.

Responder

Normally I don’t read post on blogs, but I wish to say that this write-up very
compelled me to check out and do so! Your writing taste
has been amazed me. Thank you, quite nice article.

Responder

Magnificent beat ! I would like to apprentice whilst you amend your web site, how can i
subscribe for a blog website? The account helped me a appropriate deal.
I were a little bit familiar of this your broadcast offered
bright transparent concept

Responder

Greetings I am so thrilled I found your site, I really found you by mistake, while I
was searching on Digg for something else, Anyhow I am here now and would just like to say thank you for a incredible
post and a all round interesting blog (I also love the theme/design), I don’t
have time to browse it all at the minute but I have book-marked it and also
included your RSS feeds, so when I have time I will be back to
read much more, Please do keep up the great job.

Responder

It’s an amazing article designed for all the internet visitors; they will obtain benefit from it I am sure.

Responder

Pretty! This has been a really wonderful post. Many thanks for supplying
this info.

Responder

Greate article. Keep posting such kind of info on your blog.
Im really impressed by your blog.
Hello there, You have done an excellent job. I will definitely digg it and in my view
suggest to my friends. I am sure they will be benefited from this
web site.

Responder

Normally I don’t read post on blogs, but I wish to say that this write-up very compelled
me to take a look at and do so! Your writing taste has been surprised me.
Thanks, quite great post.

Responder

Wonderful blog! Do you have any tips and hints for aspiring
writers? I’m planning to start my own site soon but I’m a little lost on everything.

Would you advise starting with a free platform like WordPress or go for a paid option? There are so many choices out there that
I’m totally overwhelmed .. Any recommendations? Bless you!

Responder

I’d like to thank you for the efforts you have put in writing this website.
I am hoping to view the same high-grade content from you in the future as well.
In fact, your creative writing abilities has motivated me to get my own, personal
blog now 😉

Responder

Hello there, I do believe your website may be having internet browser compatibility issues.
Whenever I look at your website in Safari, it looks fine however, when opening in Internet Explorer, it has some overlapping issues.

I merely wanted to provide you with a quick heads up!
Other than that, wonderful website!

Responder

Do you have a spam problem on this website; I also am
a blogger, and I was wondering your situation; many of us have developed some nice procedures and
we are looking to swap methods with other folks, be sure to shoot me an email if interested.

Responder

Thanks very interesting blog!

Responder

I like what you guys are up too. This type of clever work and coverage!
Keep up the amazing works guys I’ve added you guys to my personal blogroll.

Responder

Have you ever thought about adding a little bit more than just your articles?

I mean, what you say is fundamental and all. Nevertheless think
of if you added some great photos or video clips to give your posts more, «pop»!
Your content is excellent but with images and clips, this site could undeniably be one of the most
beneficial in its field. Great blog!

Responder

You need to be a part of a contest for one of the most useful sites on the
net. I am going to highly recommend this site!

Responder

Generally I don’t learn post on blogs, however I wish to
say that this write-up very pressured me to check out and
do it! Your writing taste has been amazed me. Thank you,
quite great article.

Responder

I am actually grateful to the owner of this website who has shared this enormous paragraph at here.

Responder

Very quickly this web page will be famous among all blogging people, due to it’s pleasant articles
or reviews

Responder

Hello everyone, it’s my first go to see at this site, and post is really fruitful in favor of me,
keep up posting such articles.

Responder

Hi! I’ve been following your website for a long time now and finally got the
bravery to go ahead and give you a shout out from Huffman Texas!
Just wanted to mention keep up the good job!

Responder

My coder is trying to convince me to move to .net from PHP.
I have always disliked the idea because of the expenses.

But he’s tryiong none the less. I’ve been using WordPress on various websites for about a year and
am worried about switching to another platform. I have heard very good things about blogengine.net.
Is there a way I can transfer all my wordpress posts into it?
Any help would be really appreciated!

Responder

Good day! I know this is somewhat off topic but I was wondering if you knew where I could find a captcha plugin for my comment form?
I’m using the same blog platform as yours and
I’m having problems finding one? Thanks a lot!

Responder

Superb blog! Do you have any tips and hints for aspiring writers?
I’m hoping to start my own blog soon but I’m a little lost on everything.
Would you propose starting with a free platform like WordPress or go for a paid option? There are so many options out there that I’m completely overwhelmed ..
Any suggestions? Cheers!

Responder

Saved as a favorite, I like your website!

Responder

I’d like to thank you for the efforts you’ve
put in writing this blog. I’m hoping to check out the same high-grade
content by you later on as well. In fact, your creative writing abilities has
motivated me to get my own, personal blog now 😉

Responder

This is a great tip particularly to those new to the blogosphere.
Short but very precise information… Appreciate your sharing this one.
A must read article!

Responder

I got this website from my buddy who told me regarding this website and at
the moment this time I am browsing this site and reading very informative posts here.

Responder

I have read so many articles regarding the blogger lovers but this piece
of writing is truly a good article, keep it up.

Responder

magnificent put up, very informative. I wonder why the opposite specialists of this sector don’t realize this.
You should continue your writing. I am confident, you’ve a great readers’ base already!

Responder

Hey there! This is kind of off topic but I need
some guidance from an established blog. Is it very difficult to set up your own blog?

I’m not very techincal but I can figure things out pretty quick.
I’m thinking about setting up my own but I’m not sure where to start.
Do you have any ideas or suggestions? Cheers

Responder

You are a very smart person!

Responder